SELECT c.name, o.* FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 3) o;

LATERAL allows subquery to reference outer columns

Placed in FROM clause

Executes once per outer row

SQL Server: CROSS APPLY (INNER) / OUTER APPLY (LEFT)

PostgreSQL: LATERAL keyword

Core references in this topic include WHERE, =, LIKE. 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.

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

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

LATERAL allows subquery to reference outer columns

Placed in FROM clause

Executes once per outer row

SQL Server: CROSS APPLY (INNER) / OUTER APPLY (LEFT)

PostgreSQL: LATERAL keyword

Use for top-N per group problems

Index columns in LATERAL subquery

Compare with window functions

Use OUTER APPLY for LEFT JOIN behavior

Test performance with real data

Index columns in LATERAL subquery

Limit rows in LATERAL subquery

Compare with window functions

Filter outer query to reduce executions

Use EXPLAIN to analyze

Not indexing LATERAL subquery columns