Real-world queries almost always span more than one table, which is where joins come in. A JOIN combines rows from two or more tables based on a related column, typically a primary key matched against a foreign key. An INNER JOIN returns only rows where the join condition matches in both tables, while a LEFT JOIN keeps every row from the left table, filling in NULLs where the right table has no match; a RIGHT JOIN does the mirror image, and a FULL OUTER JOIN returns rows whenever there is a match in either table. A CROSS JOIN produces the Cartesian product of both inputs—every combination of rows—and is useful for generating pairings but must be used carefully to avoid explosive result sizes.
Specialized joins cover trickier cases. A SELF JOIN treats a single table as if it were two, which is invaluable for hierarchical data such as employee-manager chains. A NATURAL JOIN automatically matches on columns that share the same name, but because it relies on naming conventions it is generally avoided in production code. The USING clause is a safer explicit alternative when both tables share identically named join columns. A common point of confusion is the difference between ON and WHERE: ON expresses the join condition and preserves unmatched rows in outer joins, while WHERE filters rows after the join has been performed; applying a filter with WHERE on the unmatched side of an outer join will silently remove those rows.
Sometimes the goal is to combine result sets rather than rows from related tables. UNION stacks the results of two SELECT queries and removes duplicates, while UNION ALL keeps every row and runs faster. INTERSECT returns rows that appear in both queries, and EXCEPT (called MINUS in some dialects) returns rows in the first query but not the second. Subqueries—queries nested inside another query—offer another way to combine data: a correlated subquery references the outer query and is evaluated once per outer row, while EXISTS and NOT EXISTS test whether a subquery produces any rows at all.