SQL Joins

📋 Quick Reference

---

🎮 Interactive Visualizer

Watch how different joins combine data from two tables:

Loading visualizer...

1. See INNER JOIN - only matching rows
2. Watch LEFT JOIN - all from left, nulls for unmatched right
3. Compare with RIGHT JOIN - all from right
4. Try FULL JOIN - all rows, nulls on both sides

---

🔧 Join Implementations

INNER JOIN

SQL(10 lines)
Code
Loading syntax highlighter...

employees              departments           Result
┌────┬───────┬────┐   ┌────┬─────────┐     ┌───────┬─────────┐
│ id │ name  │dept│   │ id │ name    │     │ e.name│ d.name  │
├────┼───────┼────┤   ├────┼─────────┤     ├───────┼─────────┤
│ 1  │ Alice │ 1  │   │ 1  │ Sales   │ →   │ Alice │ Sales   │
│ 2  │ Bob   │ 2  │   │ 2  │ IT      │     │ Bob   │ IT      │
│ 3  │ Carol │ 3  │   │ 4  │ HR      │     │ Carol │ Marketing│
│ 4  │ Dave  │ 5  │   └────┴─────────┘     └───────┴─────────┘
└────┴───────┴────┘                        Dave excluded (dept 5 not found)
                                           HR excluded (no employees)

LEFT JOIN (LEFT OUTER JOIN)

SQL(6 lines)
Code
Loading syntax highlighter...

Result:
┌───────┬─────────┐
│ e.name│ d.name  │
├───────┼─────────┤
│ Alice │ Sales   │
│ Bob   │ IT      │
│ Carol │ Marketing│
│ Dave  │ NULL    │  ← Dave included, no matching dept
└───────┴─────────┘

RIGHT JOIN (RIGHT OUTER JOIN)

SQL(6 lines)
Code
Loading syntax highlighter...

Result:
┌───────┬─────────┐
│ e.name│ d.name  │
├───────┼─────────┤
│ Alice │ Sales   │
│ Bob   │ IT      │
│ Carol │ Marketing│
│ NULL  │ HR      │  ← HR included, no matching employee
└───────┴─────────┘

FULL JOIN (FULL OUTER JOIN)

SQL(6 lines)
Code
Loading syntax highlighter...

Result:
┌───────┬─────────┐
│ e.name│ d.name  │
├───────┼─────────┤
│ Alice │ Sales   │
│ Bob   │ IT      │
│ Carol │ Marketing│
│ Dave  │ NULL    │  ← Unmatched left
│ NULL  │ HR      │  ← Unmatched right
└───────┴─────────┘

CROSS JOIN

SQL(5 lines)
Code
Loading syntax highlighter...

Result (4 employees × 3 departments = 12 rows):
┌───────┬─────────┐
│ e.name│ d.name  │
├───────┼─────────┤
│ Alice │ Sales   │
│ Alice │ IT      │
│ Alice │ HR      │
│ Bob   │ Sales   │
│ Bob   │ IT      │
│ ...   │ ...     │
└───────┴─────────┘

---

📊 Join Complexity

The query optimizer chooses based on:

• Table sizes
• Available indexes
• Statistics

---

✅ When to Use Each Join

INNER JOIN

• Default choice when you only want matching data
• Foreign key relationships that must exist
• Filtering - exclude rows without matches

LEFT JOIN

• Optional relationships - show all from main table
• Finding orphans - WHERE right.id IS NULL
• Aggregations - include items with zero count

RIGHT JOIN

• Rarely used - usually rewrite as LEFT JOIN
• Legacy queries - maintaining existing code
• Specific ordering - when right table is primary

FULL JOIN

• Reconciliation - find mismatches between tables
• Merging data - combine from both sources
• Finding orphans on both sides

---

🧩 Common Patterns

Finding Orphan Records

SQL(11 lines)
Code
Loading syntax highlighter...

Counting with Zero

SQL(7 lines)
Code
Loading syntax highlighter...

Self Join

SQL(4 lines)
Code
Loading syntax highlighter...

Multiple Joins

SQL(10 lines)
Code
Loading syntax highlighter...

---

⚠️ Common Pitfalls

1. Forgetting NULL in LEFT/RIGHT JOIN

SQL(10 lines)
Code
Loading syntax highlighter...

2. Accidental Cross Join

SQL(7 lines)
Code
Loading syntax highlighter...

3. Ambiguous Columns

SQL(9 lines)
Code
Loading syntax highlighter...

4. Joining on Non-Indexed Columns

SQL(10 lines)
Code
Loading syntax highlighter...

5. Duplicating Rows Unexpectedly

SQL(11 lines)
Code
Loading syntax highlighter...

---

🎯 Interview Practice

Test your SQL JOIN knowledge with 10 curated interview questions:

🎤 SQL Join Interview Mode - Coming Soon

---

🎤 Interview Tips

"

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right, with NULL for non-matches.

"

To find mismatches between two tables - rows that exist in one but not the other. Useful for data reconciliation, migration validation, or finding orphan records on both sides.

"

Use LEFT JOIN with NULL check:

SELECT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL

"

INNER JOIN is often fastest (fewer rows). JOIN performance depends on indexes, table sizes, and join algorithm (nested loop, hash, merge). Always index foreign key columns.

---

📚 Series Navigation

---