Join Table Data Using Inner and Outer Joins
In this example, you create two sample tables, combine the data into one table using different INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN syntaxes, and compare the different joined results.
Create Tables
This examples uses two tables:
-
Sample.HighestPeaks — Elevation (in feet) of mountains with the highest peaks, worldwide.
-
Sample.Himalayas — Names of mountains in the Himalayas.
Although not specified in this example, assume that the MountainID and PeakID columns of both tables are foreign key references to a larger mountain database. Therefore, rows with the same ID column value in both tables refer to the same mountain.
Create the Sample.HighestPeaks table and insert three rows of data. Display the table.
CREATE TABLE Sample.HighestPeaks (
PeakID INTEGER UNIQUE NOT NULL,
Elevation INTEGER NOT NULL)
INSERT INTO Sample.HighestPeaks VALUES (1, 29032)
INSERT INTO Sample.HighestPeaks VALUES (2, 28251)
INSERT INTO Sample.HighestPeaks VALUES (3, 28169)
SELECT * FROM Sample.HighestPeaks
PeakID |
Elevation |
1 |
29032 |
2 |
28251 |
3 |
28169 |
Create the Sample.Himalayas table and insert three rows of data. The omitted MountainID of 2 is intentional. Assume that the mountain with an ID of 2 is not in the Himalayas. Display the table.
CREATE TABLE Sample.Himalayas (
MountainID INTEGER UNIQUE NOT NULL,
Name VARCHAR(30) UNIQUE NOT NULL)
INSERT INTO Sample.Himalayas VALUES (1, 'Everest')
INSERT INTO Sample.Himalayas VALUES (3, 'Kangchenjunga')
INSERT INTO Sample.Himalayas VALUES (4, 'Lhotse')
SELECT * FROM Sample.Himalayas
MountainID |
Name |
1 |
Everest |
3 |
Kangchenjunga |
4 |
Lhotse |
Join on Identically Named Columns Across Two Tables
This example shows the different syntaxes you can use when joining columns that have identical names across the two tables.
Consider two tables:
-
Patient — Contains information about patients, including an ID code for the patient’s primary doctor, DocID.
-
Doctor — Contains information about doctors, including their ID code, DocID.
This INNER JOIN returns the patient and doctor names.
SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
ON Patient.DocID = Doctor.DocID
Because the joining columns have the same name in both tables (DocID), you can replace the ON clause with a USING clause. With this syntax, you specify only the column, in parentheses, and omit the table names.
SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
USING (DocID)
You can also specify the USING clause with a LEFT OUTER JOIN or RIGHT OUTER JOIN, but the FULL OUTER JOIN is not supported.
SELECT Patient.PName, Doctor.DName
FROM Patient
RIGHT OUTER JOIN Doctor
USING (DocID)
SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
USING (DocID)
If DocID is the only identically named column across the two tables, then you can simplify further and use the NATURAL JOIN syntax.
SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL INNER JOIN Doctor
SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL LEFT OUTER JOIN Doctor
SELECT Patient.PName, Doctor.DName
FROM Patient
NATURAL RIGHT OUTER JOIN Doctor
If the two tables contain other identical columns, then NATURAL JOIN also links those columns in the join operation. For greater specificity over the columns being joined, use the USING or ON clauses. Full outer joins do not support NATURAL JOINs.
Set Additional Restrictions on Joined Data
This example shows how the setting of additional restrictions can affect the returned data from various joins.
Consider two tables:
-
Patient — Contains information about patients, including an ID code for the patient’s primary doctor, DocID.
-
Doctor — Contains information about doctors, including their ID code, DocID.
This INNER JOIN returns the patient and doctor names of doctors who are over 45 years old.
SELECT Patient.PName, Doctor.DName
FROM Patient
INNER JOIN Doctor
ON Patient.DocID = Doctor.DocID AND Doctors.Age > 45
Performing a LEFT OUTER JOIN of the same query does not eliminate NULL values in the non-matching rows of the table being joined. For example, this LEFT OUTER JOIN still returns NULL values in the Doctor.DName column.
SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID AND Doctors.Age > 45
You can eliminate NULL values by moving the age condition into the WHERE clause, which processes after the join operation. However, this effectively converts the query into an INNER JOIN. For example, this query is equivalent to the first query in this example:
SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID
WHERE Doctors.Age > 45
Adding an IS NULL clause preserves the original LEFT OUTER JOIN behavior but is more verbose than the original LEFT OUTER JOIN query.
SELECT Patient.PName, Doctor.DName
FROM Patient
LEFT OUTER JOIN Doctor
ON Patient.DocID = Doctor.DocID
WHERE Doctors.Age > 45 AND Doctors.Age IS NULL
This behavior is similar for RIGHT OUTER JOIN operations. In a FULL OUTER JOIN, specifying conditions does not affect which rows are returned, because the operation returns all rows from both tables, regardless of matches.