Now that youve learned the basics of SQL, its 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.
Well first explore the use of a basic JOIN operation to combine data from two tables. In future installments, well explore the use of outer and inner joins to achieve added power.
Well continue with our example using the PERSONAL_INFO table, but first well need to add an additional table to the mix. Lets 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. Youll notice that it doesnt contain any information about the employee other than the employee number. Its then easy to imagine many scenarios where we might want to combine information from the DISCIPLINARY_ACTION and PERSONAL_INFO tables.
Assume weve 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.