**OFFSET/FETCH**: SELECT * FROM table ORDER BY column OFFSET n ROWS FETCH NEXT m ROWS ONLY;

**Keyset Pagination**: SELECT TOP n * FROM table WHERE key_column < @last_value ORDER BY key_column DESC;

**ROW_NUMBER**: ROW_NUMBER() OVER (ORDER BY column) - Unique sequential numbers

**RANK**: RANK() OVER (ORDER BY column) - Same rank for ties, gaps after ties

**DENSE_RANK**: DENSE_RANK() OVER (ORDER BY column) - Same rank for ties, no gaps

**PARTITION BY**: FUNCTION() OVER (PARTITION BY group_column ORDER BY sort_column)

**Window Frames**: FUNCTION() OVER (ORDER BY column ROWS BETWEEN n PRECEDING AND CURRENT ROW)

**NTILE**: NTILE(n) OVER (ORDER BY column) - Divide into n equal buckets

OFFSET/FETCH syntax: ORDER BY column OFFSET n ROWS FETCH NEXT m ROWS ONLY (ORDER BY is required)

OFFSET alone skips rows without returning any (FETCH NEXT is optional but usually needed)

Keyset pagination: WHERE key_column < @last_value ORDER BY key_column DESC (no OFFSET)

ROW_NUMBER: ROW_NUMBER() OVER (ORDER BY column) - Unique sequential numbers (1, 2, 3, 4)

RANK: RANK() OVER (ORDER BY column) - Same rank for ties, gaps after ties (1, 2, 2, 4)

DENSE_RANK: DENSE_RANK() OVER (ORDER BY column) - Same rank for ties, no gaps (1, 2, 2, 3)

PARTITION BY: FUNCTION() OVER (PARTITION BY group ORDER BY sort) - Resets calculation per group

Window frames: ROWS BETWEEN n PRECEDING AND CURRENT ROW - Physical rows, RANGE for logical range

OFFSET/FETCH (ANSI standard): ORDER BY column OFFSET n ROWS FETCH NEXT m ROWS ONLY

LIMIT/OFFSET: ORDER BY column LIMIT m OFFSET n (also supports OFFSET/FETCH)

LIMIT: ORDER BY column LIMIT n, m (offset, count) or LIMIT m OFFSET n

ROW_NUMBER or OFFSET/FETCH (12c+): ROW_NUMBER() OVER (ORDER BY column) or OFFSET n ROWS FETCH NEXT m ROWS ONLY

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

AND

Requires every condition in the boolean expression to evaluate to TRUE.

condition_a AND condition_b

AND has higher precedence than OR.

OR

Matches rows when at least one condition is TRUE.

condition_a OR condition_b

Use parentheses when mixing OR with AND.