Answer
SQL JOINs Explained
Sample Tables
SQL
-- employees table
| id | name | dept_id |
|----|---------|---------|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 30 |
| 4 | Diana | NULL | ← no department
-- departments table
| id | dept_name |
|----|-------------|
| 10 | QA |
| 20 | Development |
| 40 | HR | ← no employees
INNER JOIN — Only Matching Rows
Returns rows where the join condition is met in both tables.
SQL
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- Result:
-- Alice | QA
-- Bob | Development
-- (Charlie dept_id=30 not in departments → excluded)
-- (Diana dept_id=NULL → excluded)
-- (HR has no employees → excluded)
LEFT JOIN (LEFT OUTER JOIN) — All Left + Matching Right
Returns all rows from left table + matching rows from right. Non-matching right rows get NULL.
SQL
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- Result:
-- Alice | QA
-- Bob | Development
-- Charlie | NULL ← dept_id=30 not in departments
-- Diana | NULL ← dept_id is NULL
RIGHT JOIN (RIGHT OUTER JOIN) — All Right + Matching Left
Returns all rows from right table + matching rows from left.
SQL
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;
-- Result:
-- Alice | QA
-- Bob | Development
-- NULL | HR ← HR has no employees
FULL OUTER JOIN — All Rows From Both Tables
Returns all rows from both tables. NULLs fill gaps where no match exists.
SQL
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
-- Result:
-- Alice | QA
-- Bob | Development
-- Charlie | NULL ← no matching dept
-- Diana | NULL ← no dept_id
-- NULL | HR ← no employees in HR
-- MySQL doesn''t support FULL OUTER JOIN — emulate it:
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;
SELF JOIN — Join a Table to Itself
SQL
-- Find employees and their managers (same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
CROSS JOIN — Every Row Combined With Every Other Row
SQL
-- Generates all combinations (Cartesian product)
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- 4 employees × 3 departments = 12 rows
In QA — Verify Referential Integrity
SQL
-- Find orders with no matching customer (data integrity issue)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;