Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 88

do not match any foreign key value in the ORDER_ITEM, and because they have no match,
they do not appear in the result of this join statement. What can we do about this case when
we are creating an SQL query?
Consider the STUDENT and LOCKER tables in Figure 2-23(a), where we have drawn two
tables to highlight the relationships between the rows in each table. The STUDENT table
shows the StudentPK (student number) and StudentName of students at a university. The
LOCKER table shows the LockerPK (locker number) and LockerType ( full size or half size) of
lockers at the recreation center on campus. If we run a standard join between these two tables
as shown in SQL-QUERY-CH02-64, we get a table of students who have lockers assigned to
them together with their assigned locker. This result is shown in Figure 2-23(b).
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Query-CH02-64 *** */
SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT, LOCKER
WHERE STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY StudentPK;
The type of SQL join is known as an SQL inner join , and we can also run the query
using SQL JOIN ON syntax using the SQL INNER JOIN phrase . This is shown in SQL
QUERY-CH02-65, which produces exactly the same result shown in Figure 2-23(b).
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Query-CH02-65 *** */
SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT INNER JOIN LOCKER
ON STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY StudentPK;
Now, suppose we want to show all the rows already in the join, but also want to show any
rows (students) in the STUDENT table that are not included in the inner join. This means that
we want to see all students , including those who have not been assigned to a locker. To do this,
we use the SQL outer join , which is designed for this very purpose. And because the table we
want is listed first in the query and is thus on the left side of the table listing, we specifically
use an SQL left outer join , which uses the SQL LEFT JOIN syntax . This is shown in SQL
QUERY-CH02-66, which produces the results shown in Figure 2-23(c).
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Query-CH02-66 *** */
SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT LEFT OUTER JOIN LOCKER
ON STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY StudentPK;
In the results shown in Figure 2-23(c), note that all the rows from the STUDENT table are
now included and that rows that have no match in the LOCKER table are shown with NULL
values. Looking at the output, we can see that the students Adams and Buchanan have no
linked rows in the LOCKER table. This means that Adams and Buchanan have not been as-
signed a locker in the recreation center.
If we want to show all the rows already in the join, but now also any rows in the LOCKER
table that are not included in the inner join, we specifically use an SQL right outer join , which
uses the SQL RIGHT JOIN syntax because the table we want is listed second in the query
and is thus on the right side of the table listing. This means that we want to see all lockers ,