Database Reference
In-Depth Information
6.4 Joining Multiple Tables with SQL
If two tables are related with a primary-foreign key pair, you can join them by matching the values
in the primary key and the foreign key. To do so, you need to add a join condition in the WHERE
clause to match the values of the primary key column and the foreign key column. he columns
in the join condition must have the same domain. he following statement shows a query with
join conditions:
SELECT column1, column2, . . .
FROM table1, table2, . . .
WHERE table1.column
=
table2.column, . . .
In this query, join conditions are used to merge the rows from two or more tables. In SQL
Database, tables can also be joined by specifying table names in the FROM clause with the key-
word JOIN. It may be clearer if we separate join conditions from search conditions by placing join
conditions in the FROM clause and search conditions in the WHERE clause. he following query
shows how to separate the join conditions from the search conditions:
SELECT column1, column2, . . .
FROM table1 JOIN table2 ON table1.column
=
table2.column
WHERE search_condition
Joins can be further categorized as inner join, outer join, or self-join. Like most DBMS packages
on the market, SQL Database supports the inner join, left outer join, right outer join, and self-join.
6.4.1 Inner Join
he most commonly used join is the inner join. For two tables to join, they must share a common
column such as a primary-foreign key pair. Each value in the column from the irst table is used to
match the values in the related column from the second table. he inner join only uses the values
that have an exactly match in the join condition. As an example, let us suppose that we want to
retrieve information about the students along with the classes they have taken. To accomplish this
task, we use the StudentID column in the join condition as shown in Figure 6.39.
In the output, the students and class information are printed if the students whose id values
in the STUDENT table match the id values in the STUDENT_CLASS table. In the WHERE
clause, the common column StudentID is used in the join condition. To indicate the tables the
StudentID column belongs to, the aliases of the tables are placed in front of StudentID. Another
way to join the two tables is to specify the inner join in the FROM clause as shown below:
SELECT FirstName, LastName, ClassID, Grade
FROM STUDENT S JOIN STUDENT_CLASS C
ON S.StudentID
=
C.StudentID
6.4.2 Outer Join
SQL Database supports three types of outer joins: left, right, and full. A
left outer join
includes
all rows from the left table, even those with no corresponding rows from the right table. he miss-
ing values from the right table are placed with NULL in the output. he example in Figure 6.40
