WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

Start with WITH keyword to define CTE

CTE name followed by AS and subquery in parentheses

Multiple CTEs separated by commas (not multiple WITH keywords)

Later CTEs can reference earlier CTEs in same query

Main query follows CTE definitions and can reference any CTE

CTE exists only during query execution - disappears after

CTE names must be unique within the query

Can use CTEs in SELECT, FROM, WHERE, and JOIN clauses

No semicolon between CTE definitions - only after final query

CTE can contain any valid SELECT statement including JOINs and aggregations

Full CTE support. MATERIALIZED and NOT MATERIALIZED hints available. Excellent optimizer.

Full CTE support in MySQL 8.0+. No explicit materialization control. Good performance.

Full CTE support. Usually materializes CTEs. Excellent optimizer. No explicit hints needed.

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.

<, >, <=, >=

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

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

ANY / ALL

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

salary > ALL (SELECT salary FROM interns)

DATE

Stores a calendar date without any time-of-day component.