RIGHT JOIN, FULL OUTER JOIN and CROSS JOIN

RIGHT JOIN: keeping the right table

The LEFT JOIN keeps all the rows from the left. Its symmetric counterpart, the RIGHT JOIN (or RIGHT OUTER JOIN), keeps all the rows from the right (the one after the JOIN), whether or not they have a match on the left. When there is no match, the columns of the left table are left as NULL.

SELECT products.name, categories.name
FROM products
RIGHT JOIN categories ON products.category_id = categories.id;

Here the right is categories, so all categories appear, even those with no products (with NULL in the product column). Notice that A RIGHT JOIN B gives the same result as B LEFT JOIN A: they are two ways of writing the same thing. That is why in practice the LEFT JOIN is usually preferred, by reordering the tables.

FULL OUTER JOIN: keeping both sides

The FULL OUTER JOIN combines the two: it keeps all the rows from both tables. Those with a match are paired; those without still appear with NULL on the missing side.

SELECT products.name, categories.name
FROM products
FULL OUTER JOIN categories ON products.category_id = categories.id;

In the result you get both the products without a category (with NULL in the category name) and the categories without products (with NULL in the product name). It is the union of what a LEFT JOIN and a RIGHT JOIN would return.

Compatibility note: SQLite only supports RIGHT JOIN and FULL OUTER JOIN since version 3.39 (2022). In older versions you would have to simulate them by reordering tables (LEFT JOIN) or with UNION.

CROSS JOIN: the Cartesian product

The CROSS JOIN has no ON: it combines each row on the left with each row on the right. It is the Cartesian product. If A has 3 rows and B has 4, the result has 3 × 4 = 12 rows.

-- All combinations of size and color.
SELECT sizes.value, colors.value
FROM sizes
CROSS JOIN colors;

It is useful for generating all possible combinations (sizes × colors, days × hours...). You have to use it with care: the number of rows grows very fast. In fact, an INNER JOIN is nothing more than a CROSS JOIN to which the ON condition is then applied as a filter.

Examples

FULL OUTER JOIN: products and categories, whatever is missing

SELECT products.name AS product, categories.name AS category
FROM products
FULL OUTER JOIN categories ON products.category_id = categories.id
ORDER BY category, product;

CROSS JOIN: all combinations of size and color

SELECT s.value AS size, c.value AS color
FROM sizes AS s
CROSS JOIN colors AS c
ORDER BY s.value, c.value;