SQL JOINS

Complete Visual Guide — For Beginners to Advanced

Our Example Tables (Used Throughout This Guide)

TABLE 1 (T1) TABLE 2 (T2)

Value Value
0 0
0 0
1 1
1 NULL
1
NULL
NULL

■ GOLDEN RULE — NEVER FORGET: NULL = NULL is NEVER TRUE in SQL! NULL matches NOTHING
— not even another NULL!

Quick Results Summary

JOIN Type What It Returns Row Count

INNER JOIN Only rows that MATCH in BOTH tables 7 rows

LEFT JOIN ALL rows from T1 + matching from T2 9 rows

RIGHT JOIN ALL rows from T2 + matching from T1 8 rows

FULL OUTER JOIN ALL rows from BOTH tables 10 rows

CROSS JOIN Every T1 row × Every T2 row 28 rows

Memory Tricks:
JOIN Think of it as... Memory Trick

INNER Only mutual friends INNER = INTERSECTION = Common only

LEFT Left table is the boss LEFT is BOSS — everyone from left appears!

RIGHT Right table is the boss RIGHT is BOSS — everyone from right appears!

FULL OUTER Everyone is invited FULL = ALL from both, NULLs fill gaps

CROSS Everyone meets everyone CROSS = MULTIPLY = T1 rows x T2 rows

 JOIN 1 — INNER JOIN
Only show rows that have a MATCH in BOTH tables
Think of it like: Two friend groups — only show people who exist in BOTH groups!

How Matching Works:

T1 Value T2 Value Match? Why?

0 0 ■ YES Same value — matches!

0 0 ■ YES T1 has two 0s, T2 has two 0s = 2×2 = 4 combinations

1 1 ■ YES T1 has three 1s, T2 has one 1 = 3×1 = 3 combinations

NULL anything ■ NO NULL never matches ANYTHING!

anything NULL ■ NO T2's NULL also matches nothing!

Visual — Who Gets Included:

T1 Arrow T2 Result

0 ■■■■■■■ 0 ■ INCLUDED

0 ■■■■■■■ 0 ■ INCLUDED (4 combinations total for 0s)

1 ■■■■■■■ 1 ■ INCLUDED

1 ■■■■■■■ 1 ■ INCLUDED (3 combinations total for 1s)

1 ■■■■■■■ 1 ■ INCLUDED

NULL ✖ No match ■ DROPPED — NULL never matches!

NULL ✖ No match ■ DROPPED — NULL never matches!

NULL ■ DROPPED — T2 NULL also never matches!

RESULT — 7 Rows Returned

T1 Value T2 Value Source

0 0 T1 first 0 + T2 first 0

0 0 T1 first 0 + T2 second 0

0 0 T1 second 0 + T2 first 0

0 0 T1 second 0 + T2 second 0

1 1 T1 first 1 + T2 first 1

1 1 T1 second 1 + T2 first 1

1 1 T1 third 1 + T2 first 1

SELECT [Link], [Link] FROM T1 INNER JOIN T2 ON [Link] = [Link];

■ Key Point: INNER JOIN is the most common join. Only returns rows where BOTH sides have a matching value.
NULLs are always excluded!
 JOIN 2 — LEFT JOIN
Show ALL rows from LEFT table (T1) + matching rows from T2. No match? Fill T2 side with
NULL.
Think of it like: T1 is the BOSS — every T1 row appears no matter what! T2 only shows up if it matches.

How It Works — Row by Row:

T1 Status T2 Result

0 ■■■■■■■ MATCH 0 ■ Both shown — 4 combinations for 0+0

0 ■■■■■■■ MATCH 0 ■ Both shown

0 ■■■■■■■ MATCH 0 ■ Both shown

0 ■■■■■■■ MATCH 0 ■ Both shown

1 ■■■■■■■ MATCH 1 ■ Both shown — 3 combinations for 1+1

1 ■■■■■■■ MATCH 1 ■ Both shown

1 ■■■■■■■ MATCH 1 ■ Both shown

NULL NO MATCH — but T1 is BOSS! ■■ T1 NULL kept, T2 side = NULL

NULL NO MATCH — but T1 is BOSS! ■■ T1 NULL kept, T2 side = NULL

T2 NULL — no T1 match NULL ■ T2 NULL DROPPED — no left match!

RESULT — 9 Rows Returned

T1 Value T2 Value Note

0 0 Matched rows

0 0 Matched rows

0 0 Matched rows

0 0 Matched rows

1 1 Matched rows

1 1 Matched rows

1 1 Matched rows

NULL NULL ■■ T1 NULL kept — T2 side filled with NULL

NULL NULL ■■ T1 NULL kept — T2 side filled with NULL

SELECT [Link], [Link] FROM T1 LEFT JOIN T2 ON [Link] = [Link];

■ Key Point: T1's NULLs ARE kept (they're from the LEFT table — the BOSS!). T2's NULL is DROPPED (no T1 row
matched it). T2 side shows NULL where no match found.
 JOIN 3 — RIGHT JOIN
Show ALL rows from RIGHT table (T2) + matching rows from T1. No match? Fill T1 side with
NULL.
Think of it like: T2 is the BOSS now — every T2 row appears no matter what! T1 only shows if it matches.

How It Works — Row by Row:

T1 Status T2 Result

0 ■■■■■■■ MATCH 0 ■ Both shown — 4 combinations for 0+0

0 ■■■■■■■ MATCH 0 ■ Both shown

0 ■■■■■■■ MATCH 0 ■ Both shown

0 ■■■■■■■ MATCH 0 ■ Both shown

1 ■■■■■■■ MATCH 1 ■ Both shown — 3 combinations for 1+1

1 ■■■■■■■ MATCH 1 ■ Both shown

1 ■■■■■■■ MATCH 1 ■ Both shown

NO MATCH — T2 is BOSS! NULL ■■ T2 NULL kept, T1 side = NULL

NULL T1 NULL — no T2 match ■ T1 NULLs DROPPED — no right match!

NULL T1 NULL — no T2 match ■ T1 NULLs DROPPED — no right match!

RESULT — 8 Rows Returned

T1 Value T2 Value Note

0 0 Matched rows

0 0 Matched rows

0 0 Matched rows

0 0 Matched rows

1 1 Matched rows

1 1 Matched rows

1 1 Matched rows

NULL NULL ■■ T2 NULL kept — T1 side filled with NULL

SELECT [Link], [Link] FROM T1 RIGHT JOIN T2 ON [Link] = [Link];

■ Key Point: T2's NULL IS kept (it's from the RIGHT table — the BOSS!). T1's NULLs are DROPPED (no T2 row
matched them). T1 side shows NULL where no match found.
 JOIN 4 — FULL OUTER JOIN
Show ALL rows from BOTH tables. No match on either side? Fill with NULL.
Think of it like: EVERYONE is invited to the party — from both groups! No one is left out!

How It Works — Row by Row:

T1 Status T2 Result

0 ■■■■■ MATCH ■■■■■ 0 ■ Both shown — 4 combinations

0 ■■■■■ MATCH ■■■■■ 0 ■ Both shown

0 ■■■■■ MATCH ■■■■■ 0 ■ Both shown

0 ■■■■■ MATCH ■■■■■ 0 ■ Both shown

1 ■■■■■ MATCH ■■■■■ 1 ■ Both shown — 3 combinations

1 ■■■■■ MATCH ■■■■■ 1 ■ Both shown

1 ■■■■■ MATCH ■■■■■ 1 ■ Both shown

NULL T1 NULL — BOTH are BOSS! ■■ T1 NULL kept, T2 = NULL

NULL T1 NULL — BOTH are BOSS! ■■ T1 NULL kept, T2 = NULL

T2 NULL — BOTH are BOSS! NULL ■■ T2 NULL kept, T1 = NULL

RESULT — 10 Rows Returned

T1 Value T2 Value Note

0 0 Matched rows (4 combinations)

0 0

0 0

0 0

1 1 Matched rows (3 combinations)

1 1

1 1

NULL NULL ■■ T1 NULL kept — T2 side = NULL

NULL NULL ■■ T1 NULL kept — T2 side = NULL

NULL NULL ■■ T2 NULL kept — T1 side = NULL

SELECT [Link], [Link] FROM T1 FULL OUTER JOIN T2 ON [Link] = [Link];

■ Key Point: BOTH T1 NULLs AND T2 NULL are kept! Everyone appears — unmatched rows get NULL on the
missing side. FULL OUTER = LEFT JOIN + RIGHT JOIN combined!
 JOIN 5 — CROSS JOIN
Every row from T1 combined with EVERY row from T2. No condition needed!
Think of it like: Every person from Group 1 shakes hands with every person from Group 2!

How It Works — Simple Math:

T1 has 7 rows × T2 has 4 rows = 28 total combinations

T1 Row T2 Row Result Row T1 Row T2 Row Result Row

T1: 0 T2: 0 (0, 0) T1: 1 T2: 0 (1, 0)

T1: 0 T2: 0 (0, 0) T1: 1 T2: 0 (1, 0)

T1: 0 T2: 1 (0, 1) T1: 1 T2: 1 (1, 1)

T1: 0 T2: NULL (0, NULL) T1: 1 T2: NULL (1, NULL)

T1: 0 T2: 0 (0, 0) T1: NULL T2: 0 (NULL, 0)

... ... ...continues for all 7 T1 rows ... ... ...28 rows total

SELECT [Link], [Link] FROM T1 CROSS JOIN T2; -- No ON condition! All combinations
returned.

■ Key Point: CROSS JOIN has NO condition. Use carefully — with large tables it creates MASSIVE results! 1000
rows × 1000 rows = 1,000,000 rows!
 BONUS — SELF JOIN
A table joined with ITSELF — used when a table has a relationship within itself!
Think of it like: An employee table where managers are ALSO employees in the same table!

Example — Employee & Manager in Same Table:

emp_id name salary manager_id

1 Alice 90000 3

2 Bob 75000 3

3 Carol 95000 NULL (she IS the boss!)

4 Dave 60000 1

5 Eve 80000 1

SELECT [Link] AS employee_name, [Link] AS manager_name FROM employees e JOIN employees m

ON e.manager_id = m.emp_id; -- e = employee (child), m = manager (parent)

employee_name manager_name Explanation

Alice Carol Alice reports to Carol (Alice manager_id=3, Carol emp_id=3)

Bob Carol Bob reports to Carol (Bob manager_id=3, Carol emp_id=3)

Dave Alice Dave reports to Alice (Dave manager_id=1, Alice emp_id=1)

Eve Alice Eve reports to Alice (Eve manager_id=1, Alice emp_id=1)

■ Key Point: Use alias! 'e' for employee side, 'm' for manager side. Join condition: e.manager_id = m.emp_id (my
boss's ID = their emp_id). Carol doesn't appear as employee because her manager_id is NULL!
 COMPLETE SUMMARY — ALL JOINS AT A GLANCE
JOIN Type Returns T1 NULLs? T2 NULLs? Row Count Use When

INNER JOIN Matching rows only ■ Dropped ■ Dropped 7 rows Need only matching data

LEFT JOIN All T1 + matching T2 ■ Kept ■ Dropped 9 rows All left data + matches

RIGHT JOIN All T2 + matching T1 ■ Dropped ■ Kept 8 rows All right data + matches

FULL OUTER All from both tables ■ Kept ■ Kept 10 rows All data from both

CROSS JOIN Every combo T1 x T2 Included Included 28 rows Cartesian product

SELF JOIN Table with itself Depends Depends Varies Hierarchical data

NULL RULES — THE MOST IMPORTANT THING IN JOINS!

Rule Example Result

NULL = NULL WHERE NULL = NULL ■ FALSE — NULL never equals NULL!

NULL = 0 WHERE NULL = 0 ■ FALSE — NULL equals nothing!

NULL = anything WHERE NULL = 'hello' ■ FALSE — NULL equals NOTHING!

Check for NULL WHERE col IS NULL ■ Use IS NULL, not = NULL

INNER JOIN + NULL [Link] join [Link] ■ No match — both dropped!

LEFT JOIN + T1 NULL T1 has NULL, no match ■ T1 NULL kept, T2 side = NULL

RIGHT JOIN + T2 NULL T2 has NULL, no match ■ T2 NULL kept, T1 side = NULL

MEMORY TRICKS — READ THIS BEFORE EVERY INTERVIEW!

JOIN One Line Memory Trick

INNER JOIN INTERSECTION — only what is COMMON in both

LEFT JOIN LEFT is BOSS — ALL from left, right fills if match, NULL if not

RIGHT JOIN RIGHT is BOSS — ALL from right, left fills if match, NULL if not

FULL OUTER EVERYONE invited — ALL rows from both, NULL fills gaps

CROSS JOIN MULTIPLY — T1 rows × T2 rows, every combination

SELF JOIN MIRROR — table talks to itself, use aliases e and m

NULL Rule NULL = NULL is ALWAYS FALSE — use IS NULL to check for NULL

INTERVIEW ANSWER — What to Say for Our Specific Example:

"INNER JOIN returns 7 rows — only the matching 0s and 1s. NULLs are dropped from both sides because NULL never
matches anything.

LEFT JOIN returns 9 rows — all 7 from T1 including its NULLs, plus T2 matches. T2's NULL is dropped because no T1 row
matched it.

RIGHT JOIN returns 8 rows — all 4 from T2 including its NULL, plus T1 matches. T1's NULLs are dropped because no T2
row matched them.

FULL OUTER JOIN returns 10 rows — all rows from both tables. T1's NULLs AND T2's NULL are all kept, with NULL filling
the unmatched side.
The most important thing to remember is the NULL rule — NULL never matches anything, not even another NULL!"

Master these joins and you'll never struggle with SQL interview questions again! ■■