SQL Join Types

Join types at a glance

Simple (inner) join

List names of all clients who have viewed a property along with any comment supplied.

SELECT c.clientNo, fName, lName, propertyNo, comment
FROM   Client c, Viewing v
WHERE  c.clientNo = v.clientNo;

• Only rows from both tables that have identical values in the clientNo columns (c.clientNo = v.clientNo) are included.
• Equivalent to the equi-join in relational algebra.

Client CR74 (Mike Ritchie) has viewed nothing, so he does not appear — that’s the key limitation that outer joins solve.

Alternative JOIN constructs

FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo   -- explicit INNER JOIN
FROM Client JOIN Viewing USING (clientNo)
FROM Client NATURAL JOIN Viewing

In each case, the FROM replaces the original FROM and WHERE. The first (ON) produces a table with two identical clientNo columns.

Sorting a join

For each branch, list numbers and names of staff who manage properties, and the properties they manage.

SELECT   s.branchNo, s.staffNo, fName, lName, propertyNo
FROM     Staff s, PropertyForRent p
WHERE    s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;

Three-table join

For each branch, list staff who manage properties, including the city in which the branch is located and the properties they manage.

SELECT   b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM     Branch b, Staff s, PropertyForRent p
WHERE    b.branchNo = s.branchNo
  AND    s.staffNo  = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;

Alternative formulation for FROM and WHERE:

FROM (Branch b JOIN Staff s USING (branchNo)) AS bs
     JOIN PropertyForRent p USING (staffNo)

Multiple grouping columns

Find the number of properties handled by each staff member.

SELECT   s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM     Staff s, PropertyForRent p
WHERE    s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;

Computing a join

The conceptual procedure for generating the result of a join:

1. Form the Cartesian product of the tables named in the FROM clause.
2. If there is a WHERE clause, apply the search condition to each row of the product table, retaining rows that satisfy it.
3. For each remaining row, determine the value of each item in the SELECT list to produce a single row in the result.
4. If DISTINCT has been specified, eliminate duplicate rows.
5. If there is an ORDER BY clause, sort the result as required.

SELECT [DISTINCT | ALL] {* | columnList}
FROM   Table1 CROSS JOIN Table2;   -- explicit Cartesian product

Outer joins — the example tables

With an inner join, if a row of one table is unmatched it is omitted from the result. Outer joins retain rows that do not satisfy the join condition.

Inner join of these tables

SELECT b.*, p.*
FROM   Branch1 b, PropertyForRent1 p
WHERE  b.bCity = p.pCity;

The result has two rows where the cities are the same. There are no rows for the branch in Bristol or the property in Aberdeen. To include unmatched rows, use an outer join.

LEFT OUTER JOIN

List branches and properties that are in the same city, along with any unmatched branches.

SELECT b.*, p.*
FROM   Branch1 b LEFT JOIN PropertyForRent1 p
       ON b.bCity = p.pCity;

• Includes rows of the first (left) table that are unmatched with rows from the second (right) table.
• Columns from the second table are filled with NULLs.

RIGHT OUTER JOIN

List branches and properties in the same city and any unmatched properties.

SELECT b.*, p.*
FROM   Branch1 b RIGHT JOIN PropertyForRent1 p
       ON b.bCity = p.pCity;

• Includes rows of the second (right) table that are unmatched with rows from the first (left) table.
• Columns from the first table are filled with NULLs.

FULL OUTER JOIN

List branches and properties in the same city and any unmatched branches or properties.

SELECT b.*, p.*
FROM   Branch1 b FULL JOIN PropertyForRent1 p
       ON b.bCity = p.pCity;

• Includes rows that are unmatched in both tables.
• Unmatched columns are filled with NULLs.

Practical patterns

SELECT c.clientNo, c.fName, c.lName
FROM   Client c LEFT JOIN Viewing v ON c.clientNo = v.clientNo
WHERE  v.clientNo IS NULL;          -- CR74 Mike Ritchie

Quick review