SELECT and Filtering

Character comparison follows the collation in effect, not numeric value: the collation decides case sensitivity, accent handling and sort order. So '10' < '9' is true because '1' sorts before '9', and whether 'ANNA' = 'anna' holds depends on the collation.

All four NULL rows collapse into a single row whose country is NULL. Duplicate elimination treats two NULLs as the same value, so the result is one NULL row plus one row per distinct non-null country.

With SELECT DISTINCT, ORDER BY may only sort by expressions that appear in the select list. After deduplication one city row stands for many users with different created_at values, so that sort key no longer has a single well-defined value.

LIKE itself decides nothing about case. The comparison follows the collation of the strings involved, and engines ship different defaults, so the same LIKE query can be case-sensitive on one database and not on another.

Nominate an escape character with the ESCAPE clause and put it in front of the wildcard you want taken literally, as in code LIKE '%!_%' ESCAPE '!'. Standard SQL defines no default escape character, so always declare one.

Standard SQL writes the slice as OFFSET n ROWS FETCH NEXT m ROWS ONLY, placed after ORDER BY. LIMIT is a widely implemented extension, not the ISO spelling; FIRST and NEXT, and ROW and ROWS, are interchangeable.

Use the standard NULL-safe comparison IS NOT DISTINCT FROM, which returns TRUE when both sides are NULL and never yields UNKNOWN. Where an engine lacks it, write a = b OR (a IS NULL AND b IS NULL).

The standard leaves it implementation-defined: an engine treats NULLs as either all-highest or all-lowest, so they cluster at one end. NULLS FIRST or NULLS LAST on a sort key states it explicitly; where the engine lacks that clause, sort by a CASE flag first.

Yes to both. ORDER BY accepts any expression over the row, including one the query does not return, and an integer literal that refers to the nth column of the select list. Ordinals are brittle: editing the SELECT list silently changes the sort.

Standard SQL uses EXTRACT(YEAR FROM order_date) for a date part and interval arithmetic such as order_date + INTERVAL '30' DAY for a derived date. Both spellings are dialect-sensitive: SQL Server uses DATEPART and DATEADD instead.

SELECT * expands to every column of every table in the FROM clause, in FROM order and then table-definition order. Joined tables that share a name yield two result columns with the same name, which breaks name-based client access.

Standard SQL concatenates with the || operator: first_name || ' ' || last_name. A NULL operand makes the whole result NULL, so wrap parts in COALESCE. SQL Server uses + or CONCAT, and MySQL treats || as OR by default.

Yes. Duplicate elimination in UNION, INTERSECT and EXCEPT compares rows with NULLs treated as equal, so two rows that are NULL in the same column collapse into one — unlike the = operator, which yields UNKNOWN for NULL = NULL.

One ORDER BY may appear at the very end of the statement, and it sorts the whole combined result rather than an individual branch. It can reference only the result's column names, taken from the first branch, or ordinal positions.

AND binds tighter than OR, so an unparenthesized OR fragment escapes the conditions concatenated before it — including tenant or ownership scoping. Wrap every generated fragment in its own parentheses and join fragments with AND.

DISTINCT only collapses rows that match in every selected column, so a one-to-many join's duplicates reappear the moment you select any column from the many side. It hides a wrong join grain rather than correcting it.

Very large literal lists hit statement-size and bind-parameter limits, and every different list length is a different statement text the engine must parse afresh. Stage the ids as a set the query can join or test against, or send them in bounded batches.

OFFSET counts positions in a result recomputed from scratch for every request. Rows inserted ahead of the offset between requests push everything later, so a row already shown reappears on the next page; deletions shift rows earlier and hide them entirely.

No. The logical order defines what a query means; it is an as-if rule. An engine may do the work in any order, with any algorithm, or skip it entirely, as long as the rows returned match what the logical order specifies.

Rows with a NULL status make both predicates UNKNOWN, and WHERE keeps only TRUE, so those rows fall out of both buckets. A predicate and its negation are not exhaustive over a nullable column.

That is a row-value IN predicate: it matches only the exact pairs (1,'A') and (2,'B'). Two separate IN lists on dept_id and grade would form the cross product and also match (1,'B') and (2,'A'). Engine support varies.

WITH TIES also returns every further row whose ORDER BY key equals the tenth row's, so the result can exceed ten rows. ONLY stops at exactly ten. WITH TIES requires an ORDER BY, since peers are defined by the sort key.

NULLIF(x, 0) returns NULL when x equals 0 and x otherwise. Dividing by it turns a would-be division-by-zero error into a NULL result, since any arithmetic with a NULL operand yields NULL.

No. A subquery, CTE or view yields a table, and a table has no order, so only the statement's outermost ORDER BY constrains the result. An inner ORDER BY is at best ignored and some engines reject it outright.

A bare name in ORDER BY resolves to the select-list alias first, while WHERE and GROUP BY see only input columns, so one identifier can mean two different things in a single statement. Qualify the base column, or do not reuse the name.

A CASE yields one value of one type, so the engine derives a single result type from all THEN and ELSE results. Branches must be type-compatible; unrelated types such as text and timestamp are a type error in strictly typed engines.

The user's own characters are still pattern metacharacters, so the filter silently over-matches: a term of _ matches every non-empty value. Escape the term's %, _ and escape character before concatenating, and declare an ESCAPE character.

The logical clause order defines which rows come back, not which expressions get evaluated. An engine may compute a select-list expression for rows a filter would have removed, so guard the arithmetic itself with NULLIF or CASE.

Multiplying exact numerics adds their scales, so a two-decimal price times a two-decimal rate yields four decimals; if the columns are binary floating point instead, the value is inexact from the start. Use DECIMAL columns and round once, deliberately.

INTERSECT binds more tightly than UNION and EXCEPT, which have equal precedence and associate left to right. Because engines have not always agreed on this, parenthesize any query that mixes set operators rather than relying on the default.