Joins are how you combine rows from multiple related tables, and choosing the right join type determines which rows appear in the output. An INNER JOIN returns only rows with matches in both tables, making it the strictest and most common form. A LEFT JOIN preserves every row from the left table and adds matched columns from the right, filling unmatched right-side columns with NULL. A RIGHT JOIN mirrors that behavior on the right side, and a FULL OUTER JOIN keeps all rows from both sides, again using NULLs where no match exists. A CROSS JOIN produces the Cartesian product — every row of one table paired with every row of the other — which is useful for combinatorial generation but can quickly produce enormous result sets.
Two special join forms deserve attention. A self join uses the same table twice with different aliases, allowing you to compare rows within that table, for example finding employees whose manager also works in the same department. A NATURAL JOIN automatically joins on every pair of columns that share a name, which is convenient but risky because adding a new identically named column later silently changes the join semantics. Most of the time you should explicitly state the join condition using ON, which accepts any arbitrary expression. The USING (col) shorthand is appropriate when both tables have a single identically named join column and you want exactly one output column for that key rather than two.
The ON clause specifies the predicate used to match rows between the two tables and behaves much like a WHERE clause for the join itself. A derived table — a subquery used in the FROM clause with an alias — can also be joined like an ordinary table, which is useful for pre-aggregating data before joining. Remember that joins participate in the FROM phase of query execution, so they are processed before WHERE and the rest of the pipeline.