RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN

RIGHT JOIN

A RIGHT JOIN keeps every row of the right table. It's a left join with the tables swapped, so most people simply write left joins and reorder the tables. (SQLite supports RIGHT and FULL joins since version 3.39; MySQL has no FULL JOIN.)

FULL OUTER JOIN

A FULL OUTER JOIN keeps unmatched rows from both sides. It's useful for comparing two lists — for example, two versions of a product catalogue:

-- standalone
CREATE TABLE catalogue_2024 (sku TEXT, price NUMERIC);
CREATE TABLE catalogue_2025 (sku TEXT, price NUMERIC);
INSERT INTO catalogue_2024 VALUES ('A1', 10), ('B2', 20), ('C3', 30);
INSERT INTO catalogue_2025 VALUES ('B2', 22), ('C3', 30), ('D4', 40);

SELECT
  COALESCE(old.sku, new.sku) AS sku,
  old.price AS price_2024,
  new.price AS price_2025,
  CASE
    WHEN old.sku IS NULL THEN 'added'
    WHEN new.sku IS NULL THEN 'removed'
    WHEN old.price <> new.price THEN 'price changed'
    ELSE 'unchanged'
  END AS change
FROM catalogue_2024 old
FULL OUTER JOIN catalogue_2025 new ON new.sku = old.sku
ORDER BY sku;

+-----+------------+------------+---------------+
| sku | price_2024 | price_2025 |    change     |
+-----+------------+------------+---------------+
| A1  |         10 | NULL       | removed       |
| B2  |         20 |         22 | price changed |
| C3  |         30 |         30 | unchanged     |
| D4  | NULL       |         40 | added         |
+-----+------------+------------+---------------+

(CASE is explained in its own lesson shortly.)

CROSS JOIN

A CROSS JOIN pairs every row of one table with every row of another — the Cartesian product. With 3 sizes and 2 colours, you get 6 combinations:

-- standalone
CREATE TABLE sizes (size TEXT);
CREATE TABLE colours (colour TEXT);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
INSERT INTO colours VALUES ('black'), ('white');

SELECT size, colour
FROM sizes
CROSS JOIN colours
ORDER BY colour, size;

+------+--------+
| size | colour |
+------+--------+
| L    | black  |
| M    | black  |
| S    | black  |
| L    | white  |
| M    | white  |
| S    | white  |
+------+--------+

Useful for generating all combinations — every product for every store, every day for every region. Used accidentally (a join with a missing ON condition), it multiplies rows and is a classic bug.

Choosing a join