Types of Joins

Joins are the backbone of relational queries. Choosing the right join determines which records appear in your final output.

The Join Family

1. INNER JOIN: Returns records that have matching values in both tables.
2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
5. CROSS JOIN: Returns the Cartesian product of the two tables.

INNER JOIN

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Only rows where u.id matches o.user_id appear. Users without orders and orders without users are excluded.

LEFT JOIN

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

All users appear. If a user has no orders, o.amount is NULL. This is the most common outer join used in interviews.

RIGHT JOIN

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

All orders appear. Orders without a valid user will have NULL for u.name. RIGHT JOIN is rarely used — most developers prefer LEFT JOIN and swap the table order.

FULL JOIN

SELECT u.name, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

All rows from both tables appear. Unmatched rows show NULLs on the opposite side. Useful for finding orphan records on either side.

CROSS JOIN

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

Every user is paired with every product. The result has rows(users) × rows(products) rows. Used for generating combinations like calendar dates × stores.

SELF JOIN

A SELF JOIN is a regular join where a table is joined with itself. It uses table aliases to distinguish the two roles:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Common use cases: employee-manager hierarchies, finding duplicates, comparing rows within the same table.

JOIN Order Matters (with LEFT JOIN)

When chaining multiple left joins, the order of tables can change results. Each left join preserves all rows from its left input, so A LEFT JOIN B LEFT JOIN C keeps all rows from A, then all rows from (A + B) when joining C.

SELECT o.order_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

SELECT u.name, SUM(o.amount) as total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

SELECT e.name AS Employee, m.name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

SELECT a.*
FROM TableA a
INNER JOIN TableB b ON a.id = b.id;

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

SELECT d.name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;