Filtering & Sorting Basics

The five clauses that make up almost every query

Every SQL query you'll write in a coding interview is some combination of these five clauses, always in the same logical order:

WHERE is evaluated before SELECT conceptually — the engine decides which rows survive first, then decides which columns of those rows to show you. This matters the moment you try to filter on a column you didn't select: it's completely legal, because WHERE never cared what you put in SELECT in the first place.

DISTINCT sits right after SELECT and removes duplicate rows from the result — not duplicate values in one column while keeping others, a common misconception. SELECT DISTINCT city, country FROM addresses removes rows that are duplicates across both columns together, not city duplicates alone.

Combining conditions: AND, OR, NOT, IN, BETWEEN

• AND/OR combine boolean conditions, with AND binding tighter than OR — WHERE a AND b OR c means (a AND b) OR c, not a AND (b OR c). Parenthesize when it matters; don't rely on remembering precedence rules under interview pressure.
• IN (v1, v2, v3) is shorthand for col = v1 OR col = v2 OR col = v3 — cleaner to read and, in practice, often better-optimized by the query planner than a long OR chain.
• BETWEEN a AND b is inclusive on both ends (col >= a AND col <= b) — a very common assumption bug is treating it as a half-open range.

The single most important gotcha on this page: NULL is not a value

NULL means "unknown" or "absent," not "empty string" or "zero." That single fact has a consequence that surprises almost everyone the first time they hit it:

= NULL doesn't error — it silently evaluates to UNKNOWN (SQL's three-valued logic: TRUE, FALSE, UNKNOWN), and a WHERE clause only keeps rows where the condition is TRUE. UNKNOWN rows are filtered out exactly like FALSE ones, with no warning. The only correct way to test for NULL is IS NULL / IS NOT NULL. This exact trap reappears in a much more dangerous form with NOT IN against a subquery — that's covered in depth in the Set Operations & Anti-Joins subtopic, but the root cause is this same three-valued logic, so internalizing it here pays off twice.

A related, smaller gotcha: NULL values sort as if they were the smallest (or, on some engines, largest) possible value — ORDER BY puts them first or last depending on the engine's default, and most engines let you override this explicitly with ORDER BY col NULLS LAST / NULLS FIRST (PostgreSQL, Oracle) or an equivalent CASE-based trick (MySQL, which lacks the syntax).

Why interviewers care

This subtopic almost never is the interview question — it's the baseline fluency the interviewer assumes so they can spend the interview watching you reason about joins, aggregation, or a tricky edge case instead of watching you recall clause order. Where it does become the question: an interviewer deliberately seeds a nullable column into the schema and watches whether you reach for = NULL or IS NULL without being told which columns are nullable. Getting this wrong on a warmup question is a worse signal than getting a hard question partially wrong, precisely because it's supposed to be automatic.

Pitfalls and interview gotchas

• = NULL instead of IS NULL. Covered above — the single most common silent bug in this entire subtopic.
• Assuming SELECT DISTINCT col1, col2 dedupes on col1 alone. It dedupes on the whole row of selected columns.
• Forgetting ORDER BY has no guaranteed default. Without an explicit ORDER BY, SQL makes no promise whatsoever about row order — a query that "happens" to return rows in insertion order today can return them in a different order tomorrow after an index is added or a query plan changes. Never rely on implicit ordering, in an interview or in production.
• LIMIT without ORDER BY. "Give me the top 5" is meaningless without a defined sort — always pair LIMIT with ORDER BY unless you genuinely don't care which rows you get.

Where this goes next

String & Date Functions (next) builds directly on WHERE/CASE to reshape values once simple comparison isn't enough — pattern matching text, extracting parts of a date, or bucketing a continuous value into a handful of labels. After that, Joins & Multi-Table Queries is where this single-table fluency gets combined across tables, which is where the majority of realistic interview questions actually live.