WITH RECURSIVE cte_name AS (

SELECT columns, 0 AS level FROM table WHERE base_condition

UNION ALL

SELECT columns, level + 1 FROM table t JOIN cte_name c ON t.parent_id = c.id WHERE c.level < 10

)

SELECT * FROM cte_name;

WITH RECURSIVE keyword required (PostgreSQL, MySQL 8.0+)

Anchor member: base case, no self-reference

UNION ALL required (not UNION - would break recursion)

Recursive member: must reference CTE itself

Termination condition mandatory: WHERE level < N or no matching rows

SQL Server: Use OPTION (MAXRECURSION N) to limit depth

Core references in this topic include WHERE, =, != / <>. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

!= / <>

Returns rows where the compared values are not equal.

column <> value

SQL supports both <> and != in many engines, but <> is the portable form.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

COUNT

Counts rows or non-NULL values depending on the argument.

COUNT(*)

SUM

Adds numeric values together across the current group or window frame.