SELECT and Filtering

Simple CASE compares one expression for equality against a list of values (CASE status WHEN 'N' THEN ...). Searched CASE takes an independent boolean predicate per WHEN, so it can test ranges, several columns, or IS NULL.

Yes. Standard SQL precedence is NOT, then AND, then OR, so a OR b AND c means a OR (b AND c). The classic bug is an OR list of values silently escaping a filter that was meant to apply to all of them.

DISTINCT applies to the whole select list, not to one column. SELECT DISTINCT city, state returns every unique city-and-state combination, so one city appears repeatedly if it occurs with several states.

BETWEEN is inclusive at both ends: x BETWEEN 10 AND 20 means x >= 10 AND x <= 20, so 10 and 20 both match. The lower bound must be written first, or the predicate matches nothing.

In a LIKE pattern, % matches any sequence of zero or more characters and _ matches exactly one character. Every other character is a literal, and the pattern must match the whole value, not just part of it.

OFFSET 20 discards the first 20 rows of the ordered result; LIMIT 10 then returns at most the next 10 — rows 21 through 30. OFFSET applies first, and LIMIT counts only rows that survive the skip.

SQL is written SELECT-first but evaluated FROM-first: FROM and its joins, then WHERE, GROUP BY, HAVING, the SELECT list, DISTINCT, ORDER BY, and finally the row limit. Each stage consumes the previous stage's output.

Comparing anything with NULL yields UNKNOWN rather than TRUE, and WHERE keeps only rows whose predicate is TRUE. So email = NULL matches nothing, not even rows whose email is NULL. Write email IS NULL.

ASC and DESC bind to one sort key each, so only first_name is descending; last_name still uses the default ASC. To reverse both, spell it out: ORDER BY last_name DESC, first_name DESC.

Any value expression: literals, arithmetic and string operators, scalar function calls such as UPPER, SUBSTRING or CAST, and CASE expressions. Each one is evaluated per row and produces a derived column, normally named with AS.

UNION combines the rows of two queries and removes duplicate rows from the combined result. UNION ALL concatenates them and keeps every row, duplicates included. UNION ALL is the cheaper choice whenever duplicates are impossible or wanted.

Branches are considered in written order and the first WHEN whose condition is TRUE supplies the result; later matching branches are never reached. Overlapping conditions are legal, so ordering the branches wrongly makes a branch unreachable.

The operands have different types, so something must convert. Standard SQL treats character and numeric values as not comparable; engines that allow the comparison apply their own implicit conversion, so results and errors vary. Compare like with like instead.

The end literal is a date, so it compares as 2024-01-31 00:00:00. A timestamp column keeps only rows at exactly midnight that day; everything later on the 31st fails the <= test. Use a half-open range instead: >= '2024-01-01' AND < '2024-02-01'.

Without ORDER BY, a SQL result has no defined order, so LIMIT 10 returns whichever ten rows the engine produced first. A different plan, different data or different timing can produce a different ten. Only a total ORDER BY makes the slice deterministic.

WHERE is evaluated before the SELECT list, so the alias does not exist yet. Repeat the expression in WHERE, or compute it in a derived table or WITH clause and filter in the outer query.

UNKNOWN propagates unless the other operand already decides the result: FALSE AND UNKNOWN is FALSE, TRUE OR UNKNOWN is TRUE, and every other combination with UNKNOWN — including NOT UNKNOWN — stays UNKNOWN.

Several engines keep integer arithmetic in the integer domain, so dividing two integers truncates the fraction: 3 / 4 becomes 0. Cast one operand to an exact numeric type first, for example CAST(wins AS DECIMAL(10,4)) / games.

A EXCEPT B returns the distinct rows produced by A that do not appear anywhere in B. Duplicates are removed by default, and the operator is not commutative — swapping the two queries changes the result.

ORDER BY constrains only the relative order of rows that differ on a sort key. Rows equal on every key you listed may be returned in any order, and SQL promises no stability across runs. Add a unique final key to make the ordering total.

It returns NULL. An omitted ELSE is defined as ELSE NULL, so any row matching no WHEN branch gets NULL rather than an empty string, a zero, or an error — and the row itself is still returned.

IN against a value list is shorthand for a chain of equality tests joined by OR: status = 'new' OR status = 'open'. It is a per-row test, so duplicates and the order of values in the list change nothing about which rows or how many rows come back.

Arithmetic on an absent value is undefined, so any expression with a NULL operand evaluates to NULL — the whole sum vanishes when bonus is missing. Substitute a default first: base_salary + COALESCE(bonus, 0).

UNION matches the two SELECT lists by position, not by name. Both must produce the same number of columns, and each positional pair must have types the engine can resolve to one common type. Result column names come from the first branch.

A double-quoted (delimited) identifier is taken literally: case-sensitive, and free to contain spaces or reserved words. An unquoted identifier is case-insensitive because the engine folds its case, so quoting can make Orders and orders different objects.

A table alias replaces the table name for that query: once orders is given the correlation name o, only o.total is a valid qualified reference. Every occurrence of a table needs its own alias to be addressable separately.

Simple CASE matches with equality, and status = NULL evaluates to UNKNOWN, never TRUE, so the branch cannot fire; a NULL status falls through to ELSE. Use a searched CASE with WHEN status IS NULL instead.

Make CASE map each value to a sort rank and use that expression as a sort key: ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 ELSE 3 END, created_at. The ranks are sorted, not displayed.

NOT binds tighter than AND and OR, so parenthesize whatever you negate. Negating a compound predicate flips the connective by De Morgan: NOT (a = 1 AND b = 2) equals a <> 1 OR b <> 2, and NOT (a = 1 OR b = 2) equals a <> 1 AND b <> 2.