Joins

Joins are how we combine data from two or more tables based on a related column. This is one of the most fundamental operations in SQL, and easily the most asked topic in database interviews.

Let’s set up our example tables first:

-- Employees table
-- id | name    | dept_id
-- 1  | Alice   | 1
-- 2  | Bob     | 2
-- 3  | Charlie | NULL    (no department assigned)
-- 4  | Diana   | 1

-- Departments table
-- id | name
-- 1  | Engineering
-- 2  | Marketing
-- 3  | HR          (no employees in this dept)

Visual Overview

INNER JOIN

Returns only the rows where there’s a match in both tables. If an employee has no department, or a department has no employees — they’re excluded.

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Diana    | Engineering

-- Charlie is excluded (dept_id is NULL, no match)
-- HR is excluded (no employees have dept_id = 3)

This is the most common join. When people just say “JOIN” without a prefix, they usually mean INNER JOIN.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. If there’s no match, the right side gets NULLs.

Think of it like: “Give me all employees, and their department if they have one.”

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Charlie  | NULL          ← included! (no matching dept)
-- Diana    | Engineering

LEFT JOIN is incredibly useful for finding “missing” data:

-- Find employees with no department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
-- Returns: Charlie

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left. If there’s no match, the left side gets NULLs.

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Diana    | Engineering
-- Bob      | Marketing
-- NULL     | HR            ← included! (no employees in HR)

In practice, most developers just use LEFT JOIN and flip the table order instead of using RIGHT JOIN. It reads more naturally.

FULL OUTER JOIN

Returns all rows from both tables. Matching rows are combined, non-matching rows get NULLs on the missing side.

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Charlie  | NULL          ← no department
-- Diana    | Engineering
-- NULL     | HR            ← no employees

Note: MySQL doesn’t support FULL OUTER JOIN directly. We’d simulate it with a UNION of LEFT and RIGHT JOIN.

CROSS JOIN

Returns the cartesian product — every row from the left table paired with every row from the right table. No ON condition needed.

If table A has 4 rows and table B has 3 rows, the result has 4 x 3 = 12 rows.

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;

-- Every employee paired with every department (12 rows)
-- Alice    | Engineering
-- Alice    | Marketing
-- Alice    | HR
-- Bob      | Engineering
-- Bob      | Marketing
-- Bob      | HR
-- ... and so on

Cross joins are rarely used in practice, but they’re useful for generating combinations (like all dates crossed with all products for a report).

SELF JOIN

A table joined with itself. We use aliases to treat the same table as two different tables. Super useful for hierarchical data.

-- Find employees and their managers
-- (assuming employees table has a manager_id column)
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
-- employee | manager
-- Alice    | Bob       (Alice reports to Bob)
-- Bob      | NULL      (Bob is the top boss)
-- Charlie  | Alice     (Charlie reports to Alice)

Multiple Joins

We often chain joins together to pull data from 3+ tables:

-- Get order details with customer and product info
SELECT
    c.name AS customer,
    p.name AS product,
    o.quantity,
    o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;

JOIN Performance Tips

1. Always join on indexed columns — joining on non-indexed columns causes full table scans.
2. Use INNER JOIN when possible — it’s typically faster than OUTER JOINs because the optimizer has more flexibility.
3. Filter early — put WHERE conditions to reduce rows before the join, not after.
4. Avoid joining too many tables — each join multiplies complexity. If we’re joining 7+ tables, we might want to rethink the query or use CTEs.

Interview Tip

The classic interview question is “explain the different types of joins.” Draw the Venn diagram on a whiteboard, use a concrete example like employees/departments, and show the actual result sets. Knowing what each join returns (and what it excludes) is more impressive than reciting definitions.