Joins

Joins combine rows from two or more tables based on a related column. They are among the most powerful features of relational databases and allow you to query data that is spread across multiple normalised tables in a single statement.

INNER JOIN

An INNER JOIN returns only rows that have a matching row in both tables:

SELECT p.id, p.title, u.username
FROM posts p
INNER JOIN users u ON p.user_id = u.id;

Rows in posts with no corresponding row in users (e.g. if the user was deleted and foreign key was not enforced) are excluded.

LEFT JOIN

A LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. Where there is no match, the right-table columns are NULL:

SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.username;

This query counts posts per user and includes users who have written zero posts.

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and matching rows from the left. In practice, you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order, so RIGHT JOINs are rarely used.

Joining Multiple Tables

You can chain multiple joins in a single query:

SELECT o.id AS order_id,
       c.name AS customer,
       p.name AS product,
       oi.quantity
FROM orders o
JOIN customers c  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p   ON oi.product_id = p.id
WHERE o.created_at >= '2024-01-01';

Self Join

A self join joins a table to itself. A classic use case is an employee table where each row stores a manager_id that references another row in the 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

A CROSS JOIN produces the Cartesian product of two tables — every row from the left paired with every row from the right. This is useful for generating combinations:

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Understanding when to use each join type and how to filter and aggregate join results is essential for building efficient, expressive SQL queries.