Mastering SQL Joins: From CROSS to LATERAL and Beyond

CROSS JOIN (Cartesian Product)

The most basic join produces a Cartesian product, pairing each row of one table with every row of another. It is useful for generating all combinations, such as pairing each day of a month with every department to create a complete day/department matrix.

Example query in PostgreSQL:

SELECT * FROM generate_series('2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day') AS days(day), departments;

Resulting rows contain every possible day‑department pair, even when no department exists for a given day.

Features : The result size equals size(days) * size(departments). If either side is empty, the result is empty.

Alternative syntax : Before ANSI JOIN, a comma‑separated table list performed the same operation. Using the explicit CROSS JOIN keyword is clearer and less error‑prone.

INNER JOIN (Theta‑JOIN)

Built on CROSS JOIN, INNER JOIN adds a predicate in the ON clause to filter the Cartesian product. The predicate can be any logical condition, allowing selective matching of rows.

Example data shows actors, film_actor, and film tables. An INNER JOIN on matching IDs returns only rows where an actor appears in a film.

Features : If either side is empty, the result is empty. The result size is less than or equal to the Cartesian product size.

Alternative syntax : The predicate can be placed in a WHERE clause after a comma‑separated list, but using explicit INNER JOIN improves readability.

EQUI JOIN

Often called an "EQUI" join, this is simply an INNER JOIN where the predicate is an equality comparison, typically on primary‑key/foreign‑key columns.

SELECT * FROM actor a JOIN film_actor fa ON a.actor_id = fa.actor_id JOIN film f ON f.film_id = fa.film_id;

It returns actors together with the films they acted in, filtering out non‑matching rows.

Features : Same as INNER JOIN; result size is bounded by the product of the participating tables.

Alternative syntax : The USING (column) clause can replace the ON clause when column names match.

OUTER JOIN

OUTER JOIN retains rows from one side even when no matching row exists on the other side. LEFT OUTER JOIN keeps all rows from the left table; RIGHT OUTER JOIN does the opposite.

In the date‑department example, a LEFT OUTER JOIN ensures every day appears at least once, with NULL for departments that were not yet created.

Features : If the preserved side contains a row, it appears in the result even when the join predicate fails.

Alternative syntax : Historically, some databases used comma‑separated lists with a WHERE clause; modern code should prefer explicit LEFT|RIGHT OUTER JOIN for clarity.

FULL OUTER JOIN

FULL OUTER JOIN retains rows from both sides, producing a row for every left‑side row and every right‑side row, filling missing columns with NULL.

It guarantees that each day and each department appear at least once in the result set.

SEMI JOIN

A SEMI JOIN returns rows from the left side that have at least one matching row on the right side, but does not duplicate the left rows for each match. It can be expressed with EXISTS or IN.

SELECT a.* FROM actor a WHERE EXISTS (SELECT 1 FROM film_actor fa WHERE fa.actor_id = a.actor_id);

Execution plans often show a “HASH JOIN (SEMI)” or “NESTED LOOP (SEMI)”. Using SEMI JOIN can stop scanning after the first match, improving performance.

ANTI JOIN

An ANTI JOIN returns rows from the left side that have **no** matching rows on the right side. It is typically written with NOT EXISTS or NOT IN (the latter being unsafe with NULL values).

SELECT * FROM actor a WHERE NOT EXISTS (SELECT 1 FROM film_actor fa WHERE fa.actor_id = a.actor_id);

Care must be taken when using NOT IN because NULL turns the predicate into UNKNOWN, yielding no rows.

LATERAL JOIN

The LATERAL keyword (supported by PostgreSQL and Oracle) allows a sub‑query on the right side to reference columns from the left side, effectively applying a function to each left‑hand row.

It can replace a CROSS JOIN that generates rows per left‑hand row, and it works with OUTER JOIN to preserve left rows when the right side returns no rows.

SQL Server uses the vendor‑specific APPLY (CROSS APPLY / OUTER APPLY) which is equivalent to LATERAL.

MULTISET

Only Oracle implements the MULTISET operator, which aggregates the result of a correlated sub‑query into a nested collection. This can be used to return a set of rows as a single column, similar to an ORM’s collection mapping.

In PostgreSQL, a similar effect can be achieved with array aggregation, producing a single column that contains an array of rows.

Conclusion

The article surveys many SQL join techniques, from the fundamental Cartesian product to advanced constructs like LATERAL and MULTISET, highlighting their semantics, typical use‑cases, alternative syntaxes, and performance considerations. Readers should now have at least one new join trick to apply in their queries.