A subquery is a query nested inside another query — in SELECT, FROM, WHERE, or HAVING — and provides a way to compute a value or set of values that the outer query then uses. A non-correlated subquery is independent of the outer query and runs once, with its result then used by the outer query. A correlated subquery, by contrast, references columns from the outer query and is re-evaluated for each outer row, which makes it more powerful but potentially expensive. A scalar subquery is the special case that returns exactly one row and one column, allowing it to be used wherever a single value is expected.
The EXISTS operator checks whether a subquery returns at least one row, returning TRUE or FALSE (and never NULL). It is usually the most efficient choice for "is there any matching row?" questions, particularly when the subquery is correlated. IN compares a value against a list or subquery result, returning TRUE if there is any match. While IN and EXISTS overlap in capability, EXISTS is typically faster for correlated existence checks because it can short-circuit on the first match rather than materializing the full list. As a rule, prefer JOINs for combining columns from related tables and reach for subqueries when you need existence checks, scalar values, or results logically independent of the joined columns.
Common Table Expressions (CTEs) name a temporary result set with the WITH keyword and can be referenced multiple times in the same query using the form WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name. CTEs often improve readability over nested subqueries, and a CTE defined in a WITH clause can reference any earlier CTE in the same clause. A recursive CTE references itself to traverse hierarchies, generate series, or walk graphs; the typical structure uses UNION ALL to combine an anchor member with the recursive member. For combining result sets across queries, set operations provide union-like behavior: UNION combines rows and removes duplicates, UNION ALL keeps duplicates and is therefore faster, INTERSECT returns rows present in both queries, and EXCEPT (called MINUS in some engines) returns rows from the first query that are not in the second. All queries combined with these operators must have the same number of columns in the same order with compatible data types.