SQL Join Types Explained: All 6 Types With Visual Examples (2026)

Our Example Data

| id | name          | role       |
|----|---------------|------------|
| 1  | Marcus Webb   | Manager    |
| 2  | Diana Cross   | Accountant |
| 3  | Victor Stone  | Security   |
| 4  | Elena Morris  | Intern     |

| id | suspect_id | statement      | date       |
|----|------------|----------------|------------|
| 1  | 1          | "I was home"   | 2026-01-15 |
| 2  | 2          | "At the bar"   | 2026-01-16 |
| 3  | 5          | "No comment"   | 2026-01-17 |

Notice: Suspects 3 and 4 have no interviews. Interview 3 references suspect_id 5, who does not exist in our suspects table.

Example:

SELECT s.name, s.role, i.statement
FROM suspects s
INNER JOIN interviews i ON s.id = i.suspect_id;

Result:

| name         | role       | statement    |
|--------------|------------|--------------|
| Marcus Webb  | Manager    | "I was home" |
| Diana Cross  | Accountant | "At the bar" |

Only 2 rows. Victor Stone and Elena Morris have no interviews. The interview referencing suspect_id 5 has no matching suspect. All three are excluded.

Example:

SELECT s.name, s.role, i.statement
FROM suspects s
LEFT JOIN interviews i ON s.id = i.suspect_id;

Result:

| name          | role       | statement    |
|---------------|------------|--------------|
| Marcus Webb   | Manager    | "I was home" |
| Diana Cross   | Accountant | "At the bar" |
| Victor Stone  | Security   | NULL         |
| Elena Morris  | Intern     | NULL         |

All 4 suspects appear. Victor and Elena have NULL statements because they were never interviewed. LEFT JOIN = LEFT OUTER JOIN (the OUTER keyword is optional).

🎯 Pro Move: Finding Missing Records

LEFT JOIN + WHERE IS NULL is a powerful pattern for finding records without matches:

-- Find suspects who have NOT been interviewed
SELECT s.name, s.role
FROM suspects s
LEFT JOIN interviews i ON s.id = i.suspect_id
WHERE i.id IS NULL;

Result:

| name          | role     |
|---------------|----------|
| Victor Stone  | Security |
| Elena Morris  | Intern   |

Two suspects still need interviews. This pattern is essential for finding gaps in your data: customers who never ordered, users who never logged in, products that never sold.

Example:

SELECT s.name, i.statement, i.date
FROM suspects s
RIGHT JOIN interviews i ON s.id = i.suspect_id;

Result:

| name         | statement     | date       |
|--------------|---------------|------------|
| Marcus Webb  | "I was home"  | 2026-01-15 |
| Diana Cross  | "At the bar"  | 2026-01-16 |
| NULL         | "No comment"  | 2026-01-17 |

All 3 interviews appear. The third interview has NULL for name because suspect_id 5 does not exist in our suspects table.

Example:

SELECT s.name, s.role, i.statement
FROM suspects s
FULL OUTER JOIN interviews i ON s.id = i.suspect_id;

Result:

| name          | role       | statement     |
|---------------|------------|---------------|
| Marcus Webb   | Manager    | "I was home"  |
| Diana Cross   | Accountant | "At the bar"  |
| Victor Stone  | Security   | NULL          |
| Elena Morris  | Intern     | NULL          |
| NULL          | NULL       | "No comment"  |

5 rows. All suspects (including those without interviews) AND all interviews (including the orphaned one referencing suspect_id 5).

Example:

SELECT s.name, l.location_name
FROM suspects s
CROSS JOIN locations l;

Result (12 rows):

| name          | location_name    |
|---------------|------------------|
| Marcus Webb   | Blue Note Lounge |
| Marcus Webb   | Parking Garage   |
| Marcus Webb   | Office 4B        |
| Diana Cross   | Blue Note Lounge |
| Diana Cross   | Parking Garage   |
| Diana Cross   | Office 4B        |
| Victor Stone  | Blue Note Lounge |
| Victor Stone  | Parking Garage   |
| Victor Stone  | Office 4B        |
| Elena Morris  | Blue Note Lounge |
| Elena Morris  | Parking Garage   |
| Elena Morris  | Office 4B        |

Every suspect paired with every location. Useful for generating combinations, test data, or date/dimension scaffolds.

Updated suspects table:

| id | name          | role       | associate_id |
|----|---------------|------------|--------------|
| 1  | Marcus Webb   | Manager    | 2            |
| 2  | Diana Cross   | Accountant | NULL         |
| 3  | Victor Stone  | Security   | 1            |
| 4  | Elena Morris  | Intern     | 3            |

Example:

SELECT
  s1.name AS suspect,
  s2.name AS known_associate
FROM suspects s1
LEFT JOIN suspects s2 ON s1.associate_id = s2.id;

Result:

| suspect        | known_associate |
|----------------|-----------------|
| Marcus Webb    | Diana Cross     |
| Diana Cross    | NULL            |
| Victor Stone   | Marcus Webb     |
| Elena Morris   | Victor Stone    |

The key is aliases: s1 and s2 let the database treat the same table as two separate tables. Marcus knows Diana. Victor knows Marcus. Diana has no known associate (NULL).

Slow vs. Optimized Query:

-- Slow: SELECT *, no early filter
SELECT *
FROM suspects s
LEFT JOIN interviews i ON s.id = i.suspect_id;

-- Faster: specific columns, early filter
SELECT s.name, i.statement
FROM suspects s
INNER JOIN interviews i ON s.id = i.suspect_id
WHERE i.date >= '2026-01-01';

The optimized version fetches fewer columns, uses INNER JOIN (stricter), and filters by date to reduce the working set.

Chaining 3 tables:

SELECT
  s.name,
  i.statement,
  l.location_name
FROM suspects s
INNER JOIN interviews i ON s.id = i.suspect_id
LEFT JOIN locations l ON i.location_id = l.id;

Result:

| name         | statement    | location_name    |
|--------------|--------------|------------------|
| Marcus Webb  | "I was home" | Blue Note Lounge |
| Diana Cross  | "At the bar" | NULL             |

The INNER JOIN filters to suspects with interviews. The LEFT JOIN adds location data where available. Diana's interview has no location_id, so location_name is NULL.