1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

JOIN Statements

By

Now that you’ve learned the basics of SQL, it’s time to move on to one of the most powerful concepts the language has to offer – the JOIN statement. Quite simply, these statements allow you to combine data in multiple tables to quickly and efficiently process large quantities of data. These statements are where the true power of a database resides.

We’ll first explore the use of a basic JOIN operation to combine data from two tables. In future installments, we’ll explore the use of outer and inner joins to achieve added power.

We’ll continue with our example using the PERSONAL_INFO table, but first we’ll need to add an additional table to the mix. Let’s assume we have a table called DISCIPLINARY_ACTION that was created with the following statement:

CREATE TABLE disciplinary_action (action_id int not null, employee_id int not null, comments char(500))

This table contains the results of disciplinary actions on company employees. You’ll notice that it doesn’t contain any information about the employee other than the employee number. It’s then easy to imagine many scenarios where we might want to combine information from the DISCIPLINARY_ACTION and PERSONAL_INFO tables.

Assume we’ve been tasked with creating a report that lists the disciplinary actions taken against all employees with a salary greater than $40,000. The use of a JOIN operation in this case is quite straightforward. We can retrieve this information using the following command:

SELECT personal_info.first_name, personal_info.last_name, disciplinary_action.comments
FROM personal_info, disciplinary_action
WHERE personal_info.employee_id = disciplinary_action.employee_id
AND personal_info.salary > 40000

As you can see, we simply specified the two tables that we wished to join in the FROM clause and then included a statement in the WHERE clause to limit the results to records that had matching employee IDs and met our criteria of a salary greater than $40,000.

Congratulations! You've learned the basic concepts behind the Structured Query Language. Stay tuned for future articles that explore these commands in-depth.

  1. About.com
  2. Technology
  3. Databases
  4. Learning SQL
  5. Basic SQL Tutorials
  6. Join Statements

©2014 About.com. All rights reserved.