Mix n match - LEFT & RIGHT joins

Introduction to SQL Server

John MacKintosh

Instructor

The rationale for LEFT and RIGHT joins

Why do we need LEFT and RIGHT joins?
One table may not have an exact match in another:
- Customer order history for marketing campaign
- Product list and returns history
- Patients admitted but not yet discharged

The rationale for LEFT and RIGHT joins

Why do we need LEFT and RIGHT joins?
One table may not have an exact match in another:
- Customer order history for marketing campaign
- Product list and returns history
- Patients admitted but not yet discharged

Admissions table

+------------+----------+
| Patient_ID | Admitted | 
|------------+----------|
| 1          | 1        |
| 2          | 1        |
| 3          | 1        |
| 4          | 1        |
| 5          | 1        |
+------------+----------+

Discharges table

+------------+------------+
| Patient_ID | Discharged | 
|------------+------------|
| 1          | 1          |
| 3          | 1          |
| 4          | 1          |
+------------+------------+

INNER JOIN:

+------------+----------+------------|
| Patient_ID | Admitted | Discharged |
|------------+----------|------------|
| 1          | 1        | 1          |
| 3          | 1        | 1          |
| 4          | 1        | 1          |
+------------+----------+------------+

LEFT JOIN:

+------------+----------+------------|
| Patient_ID | Admitted | Discharged |
|------------+----------|------------|
| 1          | 1        | 1          |
| 2          | 1        | NULL       |
| 3          | 1        | 1          |
| 4          | 1        | 1          |
| 5          | 1        | NULL       |
+------------+----------+------------+

LEFT JOIN SYNTAX

SELECT 
  Admitted.Patient_ID, 
  Admitted, 
  Discharged 
FROM Admitted 
LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;

SELECT 
  Admitted.Patient_ID, 
  Admitted, 
  Discharged 
FROM Admitted 
LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;

+------------+----------+------------|
| Patient_ID | Admitted | Discharged |
|------------+----------|------------|
| 1          | 1        | 1          |
| 2          | 1        | NULL       |
| 3          | 1        | 1          |
| 4          | 1        | 1          |
| 5          | 1        | NULL       |
+------------+----------+------------+

RIGHT JOIN

SELECT 
  Admitted.Patient_ID, 
  Admitted, 
  Discharged 
FROM Discharged 
RIGHT JOIN Admitted ON Admitted.Patient_ID = Discharged.Patient_ID;

RIGHT JOIN results

SELECT 
  Admitted.Patient_ID, 
  Admitted, 
  Discharged 
FROM Discharged 
RIGHT JOIN Admitted ON Admitted.Patient_ID = Discharged.Patient_ID;

+------------+----------+------------|
| Patient_ID | Admitted | Discharged |
|------------+----------|------------|
| 1          | 1        | 1          |
| 2          | 1        | NULL       |
| 3          | 1        | 1          |
| 4          | 1        | 1          |
| 5          | 1        | NULL       |
+------------+----------+------------+

Summary

INNER JOIN: Only returns matching rows
LEFT JOIN (or RIGHT JOIN): All rows from the main table plus matches from the joining table
NULL: Displayed if no match is found
LEFT JOIN and RIGHT JOIN can be interchangeable

Let's Practice!

Introduction to SQL Server