Lesson 1

In the previous modules, we have kept things as simple as possible by referencing only single tables in the example statements that we created. But you may recall from the module on normalization that you will often be working with more than one table when it comes to real data in your tables. Pulling this information together, and making it meaningful in the results set, requires combining, or joining, the results sets from more than one table into a single result. This module will explain how to do this, how joining helps in your analysis of the information, and how you can use joining to further your efforts to work with the information in your tables.

SQL joins are used to combine rows from two or more tables. There are in SQL four types of joins:

1. Inner join and natural joins - select records that have matching values in both tables;
2. Left Outer Join - select records from the first (left-most) table with matching right table records;
3. Right Outer Join - select record from the second (right-most) table with matching left table records;
4. Full Outer Join - selects all records that match either left or right table records.

All the queries so far have retrieved rows from a single table. This module explains how to use joins to retrieve rows from multiple tables simultaneously. Recall from “Relationships” that a relationship is an association established between common columns in two tables. A join is a table operation that uses related columns to combine rows from two input tables into one result table. You can chain joins to retrieve rows from an unlimited number of tables.
Why do joins matter? The most important database information is not so much stored in the rows of individual tables. Instead, it is the implied relationships between sets of related rows. In the sample database, for example, the individual rows of the tables authors, publishers, and titles contain important values, of course, but it is the implied relationships that let you understand and analyze your data in its entirety.
This module explains the different types of joins, why they’re used, and how to create a SELECT statement that uses them.