SQL JOINs: Types, Examples & Cheat Sheet

Databases store each kind of thing in its own table. In the Playground database an order only records who placed it as a number, user_id; the customer's name is in the users table. A JOIN brings the two together by matching orders.user_id with users.id:

SELECT o.id AS order_id, o.order_date,
       u.first_name, u.last_name, o.total
FROM orders o
JOIN users u
  ON u.id = o.user_id
ORDER BY o.id
LIMIT 5;

▶ Run this query in the SQL Playground

Every row of the result is built from two tables. ON states which rows belong together, and o and u are aliases that say which table each column comes from.

Why data is split across tables

If every order row repeated the customer's name, email and city, a change of email would have to be made in every one of that customer's orders. Keeping customers in one table and orders in another stores each fact once. This design is called normalisation, and JOIN is how the pieces are put back together for a report.

SELECT columns
FROM table_a
[INNER | LEFT | RIGHT | FULL OUTER] JOIN table_b
  ON table_a.key = table_b.key;

How the Playground tables connect

Every example on this page runs on these tables. The arrows are the foreign keys you join on:

Six joins, one question: what should happen to rows that have no match?

The four main joins on the same data

To see INNER, LEFT, RIGHT and FULL give four different answers, both tables need rows the other lacks. Take the customers who ordered in 2023 as the left table and those who ordered in 2024 as the right table, then count the rows each join returns:

WITH y2023 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
), y2024 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
)
SELECT 'INNER JOIN' AS join_type, COUNT(*) AS customers
FROM y2023 a INNER JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'LEFT JOIN', COUNT(*)
FROM y2023 a LEFT JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'RIGHT JOIN', COUNT(*)
FROM y2023 a RIGHT JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'FULL OUTER JOIN', COUNT(*)
FROM y2023 a FULL OUTER JOIN y2024 b ON a.user_id = b.user_id;

▶ Run this query in the SQL Playground

The numbers fit together: 51 + 56 − 19 = 88. The two WITH blocks are CTEs, named subqueries that stand in for two tables.

INNER JOIN returns a row for every pair that satisfies ON, and nothing else. It is the default and the most used join.

SELECT u.id, u.first_name, o.id AS order_id, o.status
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id
WHERE u.id <= 5
ORDER BY u.id, o.id;

▶ Run this query in the SQL Playground

Customer 3 has no orders and is missing. Customer 4 has three and appears three times. More in the INNER JOIN lesson, including joins across three and four tables.

LEFT JOIN keeps every row of the left table. Where there is no match, the right table's columns are NULL. The same five customers again:

SELECT u.id, u.first_name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE u.id <= 5
ORDER BY u.id, o.id;

▶ Run this query in the SQL Playground

Customer 3 is back, with NULL order columns. Filtering on that NULL finds rows with no match at all, such as customers who never ordered:

SELECT COUNT(*) AS customers_without_orders
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.id IS NULL;

▶ Run this query in the SQL Playground

The LEFT JOIN lesson covers this pattern, the ON-versus-WHERE trap and counting with zeros.

RIGHT JOIN keeps every row of the right table instead. It is a LEFT JOIN with the tables swapped: orders RIGHT JOIN users returns the same rows as users LEFT JOIN orders.

SELECT 'orders RIGHT JOIN users' AS query, COUNT(*) AS rows_returned
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id
UNION ALL
SELECT 'users LEFT JOIN orders', COUNT(*)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

Most people write LEFT JOIN only, so the complete table always comes first. See the RIGHT JOIN lesson for when it is still useful.

FULL OUTER JOIN keeps unmatched rows from both tables. NULLs can appear on either side, which makes it the join for comparing two lists:

WITH y2023 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
), y2024 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
)
SELECT a.user_id AS ordered_2023,
       b.user_id AS ordered_2024
FROM y2023 a
FULL OUTER JOIN y2024 b
  ON a.user_id = b.user_id
ORDER BY COALESCE(a.user_id, b.user_id)
LIMIT 8;

▶ Run this query in the SQL Playground

A value in both columns means the customer ordered in both years; a NULL on the left means new in 2024; a NULL on the right means they did not come back.

-- FULL OUTER JOIN in MySQL
SELECT a.id, b.id FROM a LEFT JOIN b ON b.a_id = a.id
UNION
SELECT a.id, b.id FROM a RIGHT JOIN b ON b.a_id = a.id;

Details in the FULL OUTER JOIN lesson.

CROSS JOIN has no ON clause. It pairs every row of one table with every row of the other, so 3 sizes and 2 colours give 3 × 2 = 6 combinations:

SELECT s.size, c.colour
FROM sizes s
CROSS JOIN colours c;

The Playground blocks the CROSS JOIN keyword to protect the shared database, so this runnable version writes the same join the older way, with a comma between two small lists:

SELECT s.size, c.colour
FROM (SELECT 'S' AS size UNION ALL SELECT 'M' UNION ALL SELECT 'L') AS s,
     (SELECT 'Red' AS colour UNION ALL SELECT 'Blue') AS c
ORDER BY s.size, c.colour;

▶ Run this query in the SQL Playground

Uses and safeguards are in the CROSS JOIN lesson.

A self join is not a keyword; it is any join of a table to itself. In employees, manager_id holds the id of another employee, so the table is joined to a second copy of itself under a different alias:

SELECT e.first_name AS employee, e.title,
       m.first_name AS manager
FROM employees e
LEFT JOIN employees m
  ON m.id = e.manager_id
WHERE e.id BETWEEN 8 AND 13
ORDER BY e.id;

▶ Run this query in the SQL Playground

e is the employee and m the manager. A LEFT JOIN is used so that people with no manager are kept. More in the SELF JOIN lesson.

A quick test: ask what should happen to a customer with no orders. If they should disappear, it is an INNER JOIN; if they should stay, it is a LEFT JOIN.

JOIN vs UNION vs subquery

Add one JOIN … ON per table. Each new table needs a condition linking it to a table already in the query. Four tables answer "what did the customer buy in order 1?":

SELECT u.first_name AS customer, o.order_date,
       p.name AS product, oi.quantity, oi.subtotal
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 1
ORDER BY oi.id;

▶ Run this query in the SQL Playground

Joined rows behave like any other rows, so WHERE, GROUP BY and aggregates work on top. Revenue by product category for delivered orders:

SELECT p.category,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

Most of these raise no error. The query runs and the answer is wrong.

SELECT o.id, u.first_name
FROM orders o, users u;

SELECT o.id, u.first_name
FROM orders o
JOIN users u
  ON u.id = o.user_id;

SELECT u.id, COUNT(o.id)
FROM users u
JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id;

SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id;

SELECT u.id, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.status = 'delivered';

SELECT u.id, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'delivered';

SELECT SUM(o.total)
FROM orders o
JOIN payments p
  ON p.order_id = o.id;

SELECT SUM(total)
FROM orders;

SELECT id, total
FROM orders o
JOIN users u
  ON u.id = o.user_id;

SELECT o.id, o.total
FROM orders o
JOIN users u
  ON u.id = o.user_id;

8 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show each employee's name and title with their department name, for the first ten employees by id. Table: employees, departments

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name, e.last_name, e.title,
          d.name AS department
   FROM employees e
   JOIN departments d
     ON d.id = e.department_id
   ORDER BY e.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   An inner join on the foreign key employees.department_id.

2. 2Easy List customers 20 to 30 with the id of each of their orders. Customers with no orders must still appear. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name, o.id AS order_id
   FROM users u
   LEFT JOIN orders o
     ON o.user_id = u.id
   WHERE u.id BETWEEN 20 AND 30
   ORDER BY u.id, o.id;

   ▶ Run this query in the SQL Playground

   "Must still appear" means LEFT JOIN. The result has 15 rows; customers without orders show NULL.

3. 3Medium How many products have no reviews? Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS products_without_reviews
   FROM products p
   LEFT JOIN reviews r
     ON r.product_id = p.id
   WHERE r.id IS NULL;

   ▶ Run this query in the SQL Playground

   The LEFT JOIN keeps every product, and r.id IS NULL keeps only those with no matching review.

4. 4Medium Show the ten customers who have spent the most: name, number of orders and total spent. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.first_name, u.last_name,
          COUNT(*) AS orders,
          ROUND(SUM(o.total), 2) AS total_spent
   FROM users u
   JOIN orders o
     ON o.user_id = u.id
   GROUP BY u.id, u.first_name, u.last_name
   ORDER BY total_spent DESC
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   Only customers with orders can be top spenders, so an INNER JOIN is the right choice here.

5. 5Medium For each manager, show their name and how many people report to them, most reports first. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT m.first_name, m.last_name,
          COUNT(*) AS direct_reports
   FROM employees e
   JOIN employees m
     ON m.id = e.manager_id
   GROUP BY m.id, m.first_name, m.last_name
   ORDER BY direct_reports DESC, m.id;

   ▶ Run this query in the SQL Playground

   A self join: e is the report and m the manager. 10 employees manage at least one person.

6. 6Medium For every department, count the employees who earn more than 150,000. Departments with none must show 0. Table: departments, employees

   Show solution

   SQLite · runs in the Playground

   SELECT d.name AS department,
          COUNT(e.id) AS high_earners
   FROM departments d
   LEFT JOIN employees e
     ON e.department_id = d.id
    AND e.salary > 150000
   GROUP BY d.id, d.name
   ORDER BY high_earners DESC, d.name;

   ▶ Run this query in the SQL Playground

   The salary condition is in ON so that it limits the matches without removing departments.

7. 7Challenging List the five largest order lines by subtotal, with the customer's name, the product name and the order date. Table: order_items, orders, users, products

   Show solution

   SQLite · runs in the Playground

   SELECT u.first_name, u.last_name,
          p.name AS product, o.order_date, oi.subtotal
   FROM order_items oi
   JOIN orders o   ON o.id = oi.order_id
   JOIN users u    ON u.id = o.user_id
   JOIN products p ON p.id = oi.product_id
   ORDER BY oi.subtotal DESC, oi.id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   Four tables need three join conditions, each linking a new table to one already in the query.

8. 8Challenging Using a FULL OUTER JOIN between 2023 buyers and 2024 buyers, count the customers who ordered in 2023 only, in 2024 only, and in both. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH y2023 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
   ), y2024 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
   )
   SELECT CASE WHEN a.user_id IS NULL THEN '2024 only'
               WHEN b.user_id IS NULL THEN '2023 only'
               ELSE 'both years' END AS segment,
          COUNT(*) AS customers
   FROM y2023 a
   FULL OUTER JOIN y2024 b
     ON a.user_id = b.user_id
   GROUP BY segment
   ORDER BY segment;

   ▶ Run this query in the SQL Playground

   The side that is NULL tells you which list the customer is missing from. CASE WHEN turns that into a label.