SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id; -- Optimizer typically processes small table first

Optimizer chooses join order automatically

Join order in SQL is logical, not physical

Can influence with subqueries or CTEs

STRAIGHT_JOIN forces order (MySQL)

EXPLAIN shows actual join order

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

ANY / ALL

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

salary > ALL (SELECT salary FROM interns)

INTERVAL

Represents a duration that can be added to or subtracted from dates and timestamps.

order_date + INTERVAL '7 days'

SUM

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

SUM(revenue)

GROUP BY

Collects rows into groups so aggregate functions can compute one result per group.

GROUP BY department_id

HAVING

Filters groups after aggregation has been computed.

HAVING COUNT(*) > 5

FILTER

Applies an aggregate only to rows that satisfy an extra predicate.

COUNT(*) FILTER (WHERE status = 'active')

ROWS / RANGE

Defines how a window frame is sliced around the current row.

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

Optimizer chooses join order automatically