Explain what a JOIN operation is in SQL.

Official answer

A join forms rows by combining inputs according to a condition. It does not require declared foreign keys, equally named columns or an equality condition. An inner join returns each matching pair; an outer join also retains unmatched rows from its specified side or sides with NULLs for the missing input.

This PostgreSQL example has two orders for one customer:

WITH customers(id, name) AS (VALUES (1, 'Ari'), (2, 'Bo')),
orders(id, customer_id) AS (VALUES (10, 1), (11, 1))
SELECT c.id, c.name, o.id AS order_id
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

The result contains (1, Ari, 10) and (1, Ari, 11). The customer repeats because two pairs match. Check the intended grain and relationship cardinality before summing measures across a join; DISTINCT is not a universal repair for accidental multiplication. A cross join returns every pair without a match condition.