SQL JoinsExplained

8 of 8 rows

Join↕	Returns↕	Example↕
Inner & outer
⤢INNER JOIN	Only rows where the ON condition matches in both tables. Unmatched rows on either side are dropped.	SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;
⤢LEFT OUTER JOIN	All rows from the left table. Right columns are NULL where no match exists.	SELECT u.name, o.total FROM users u LEFT OUTER JOIN orders o ON u.id = o.user_id;
⤢RIGHT OUTER JOIN	All rows from the right table. Left columns are NULL where no match exists.	SELECT u.name, o.total FROM users u RIGHT OUTER JOIN orders o ON u.id = o.user_id;
⤢FULL OUTER JOIN	All rows from both tables. Unmatched sides are filled with NULL.	SELECT u.name, o.total FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;
Cross & self
⤢CROSS JOIN	Cartesian product. Every left row pairs with every right row. No ON clause.	SELECT u.name, o.total FROM users u CROSS JOIN orders o;
⤢SELF JOIN	A table joined to itself via aliases. Compares rows within one table.	SELECT a.id AS o1, b.id AS o2 FROM orders a JOIN orders b ON a.user_id = b.user_id WHERE a.id < b.id;
Advanced
⤢LATERAL	A subquery in FROM evaluated per outer row. The subquery can reference the outer table's columns.	SELECT u.name, o.total FROM users u, LATERAL (SELECT total FROM orders WHERE user_id = u.id LIMIT 1) o;
⤢NATURAL JOIN	Joins on columns that share a name. No ON clause. Watch for unintended key collisions.	SELECT u.name, o.total FROM users u NATURAL JOIN orders o;