Main Types of JOINs

JOIN Type	What It Does
INNER JOIN	Returns rows where there’s a match in both tables.
LEFT JOIN	Returns all rows from the left table, plus matches from the right (NULL if no match).
RIGHT JOIN	Returns all rows from the right table, plus matches from the left (NULL if no match).
FULL OUTER JOIN	Returns all rows from both tables — matches where possible, NULLs where no match.
CROSS JOIN	Returns every combination of rows — Cartesian product (no ON condition).
SELF JOIN	Join a table to itself — used when you need to compare rows within the same table.

---

1. INNER JOIN

• Only rows with matching keys in both tables are returned.

SELECT * FROM A
INNER JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager

➡️ Only rows where A.id = B.a_id.

2. LEFT JOIN (LEFT OUTER JOIN)

• All rows from the left table, matched rows from right table, NULLs where no match.

SELECT * FROM A
LEFT JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
2	Bob	NULL	NULL	NULL

➡️ Bob is shown even though he has no match.

3. RIGHT JOIN (RIGHT OUTER JOIN)

• All rows from the right table, matched rows from left table, NULLs where no match.

SELECT * FROM A
RIGHT JOIN B ON A.id = B.a_id;

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
NULL	NULL	11	5	Engineer

➡️ Even if B.a_id = 5 has no match in A, it shows up with NULLs from A.

4. FULL OUTER JOIN

• All rows from both tables — match if possible, NULLs if not.

A.id	A.name	B.id	B.a_id	B.title
1	Alice	10	1	Manager
2	Bob	NULL	NULL	NULL
NULL	NULL	11	5	Engineer

➡️ Combines the behavior of LEFT and RIGHT JOINs.

5. CROSS JOIN

• Every row of A is paired with every row of B (no condition).

SELECT * FROM A
CROSS JOIN B;

If A has 3 rows and B has 2 rows ➔ 3 × 2 = 6 rows result.

A.id	A.name	B.id	B.title
1	Alice	10	Manager
1	Alice	11	Engineer
2	Bob	10	Manager
2	Bob	11	Engineer

6. SELF JOIN

• Join a table to itself — useful for hierarchies, like employees and managers.

SELECT A.name AS employee, B.name AS manager
FROM Employees A
JOIN Employees B ON A.manager_id = B.id;

employee	manager
Bob	Alice

➡️ Bob’s manager is Alice.

---

Summary Table

JOIN Type	Key Idea
INNER JOIN	Matches in both tables.
LEFT JOIN	All left rows, matched right rows.
RIGHT JOIN	All right rows, matched left rows.
FULL OUTER JOIN	All rows from both sides.
CROSS JOIN	All combinations — Cartesian product.
SELF JOIN	Table joined to itself.

🎨 Quick Sketch in Text

JOIN Type	Diagram	Description
INNER JOIN	(🟢 ∩ 🔵)	Only common (intersection).
LEFT JOIN	🟢 + (🟢 ∩ 🔵)	All A, and matches from B (else NULL).
RIGHT JOIN	🔵 + (🟢 ∩ 🔵)	All B, and matches from A (else NULL).
FULL OUTER JOIN	(🟢 ∪ 🔵)	All from A and B, NULL if no match.
CROSS JOIN	🟢 × 🔵	Every A row with every B row (Cartesian).
SELF JOIN	A ↔ A	A table joined to itself.