Using WHERE for 3 Conditions in SQL: How Compound Filters Execute

Using WHERE for 3 conditions in SQL means joining three predicates with AND, OR, or a mix of both, and wrapping OR groups in parentheses so the database applies them in the order you intend. The clause filters rows before grouping and projection, so placement and parenthesization change both results and speed. A query like WHERE status = 'open' AND region = 'west' AND amount > 500 returns only rows meeting all three tests; swap one connector to OR and the row count can jump by orders of magnitude.

How the three-condition filter executes

SQL is declarative, so you write the logic and the optimizer decides the physical order. The parser builds the WHERE clause into a Boolean expression tree, then the optimizer rewrites it before execution. Three ANDed predicates form a chain; a mixed AND/OR clause forms nested branches governed by precedence. Because AND binds tighter than OR, A OR B AND C parses as A OR (B AND C), not (A OR B) AND C. That single precedence rule explains most wrong-answer bugs with three conditions.

Execution order is not left-to-right as written. The optimizer estimates selectivity for each predicate using statistics, then evaluates the cheapest, most selective test first to discard rows early. A predicate matching 0.1 percent of rows is far better as the first filter than one matching 40 percent. Modern engines also push predicates down to the storage layer, so a filter on an indexed column can skip reading entire pages. This is why the same three conditions can run in milliseconds or minutes depending on indexing, not syntax.

Three-valued logic adds a layer. SQL treats missing data as NULL, and any comparison with NULL yields UNKNOWN, not TRUE or FALSE. A row passes WHERE only when the whole expression evaluates to TRUE. So WHERE col = 5 silently excludes rows where col is NULL, and WHERE col <> 5 also excludes them. With three conditions, a NULL in any ANDed term kills the row. Plain-language version: absent data never satisfies a filter unless you explicitly test for it.

Quantified context: cost, cardinality, and index behavior

The row counts tell the story. If condition A matches 1,000,000 rows, B matches 50,000, and C matches 200, the optimizer wants C first. Evaluating C first leaves 200 candidate rows to test against A and B, versus a million if A runs first. On a table of 50 million rows, that ordering difference can separate a 2-second query from a 90-second one. Composite indexes matter too: an index on (status, region, amount) can satisfy all three predicates in one seek when the leading columns appear in the filter.

Selectivity thresholds guide index use. A common rule of thumb is that an index helps when a predicate returns roughly 5 percent of rows or fewer; above that, a sequential scan often wins because random reads cost more than a sweep. These cutoffs are engine- and hardware-dependent, so treat 5 percent as a starting estimate, not a law. The optimizer uses its own statistics, which is why stale statistics on a fast-growing table can flip a good plan into a bad one.

OR changes the math. An OR across three conditions forces the engine to find the union of three row sets, and it cannot simply seek one index unless each branch is indexable. WHERE a = 1 OR b = 2 OR c = 3 may trigger three separate index scans merged together, or a full scan if any column lacks an index. Mixed AND/OR compounds this, because the optimizer must reason about branches. Wrapping the OR group in parentheses preserves intent; failing to do so is the most common source of silently wrong row counts at three conditions.

Edge cases that break three-condition logic

NULL handling is the first trap. WHERE a = 1 AND b = 2 AND c <> 3 returns no row where c is NULL, even though "c is not 3" feels true. Use c IS NULL OR c <> 3, or c IS DISTINCT FROM 3 on engines that support it, when NULLs should pass.

Boundary conditions on ranges are the second. BETWEEN 10 AND 20 includes both endpoints; > 10 AND < 20 excludes them. With three conditions, an off-by-one on a range endpoint quietly shifts counts. Date ranges are notorious: a timestamp of 2024-01-31 23:59:59 may fall outside a filter written as < '2024-01-31'.

Type coercion is the third. Comparing a string column to a number can force per-row conversion, disabling the index and changing match rules. Three conditions where one involves implicit casting can turn an indexed seek into a full scan.

Operator precedence with NOT is the fourth. WHERE NOT a = 1 AND b = 2 parses as (NOT a = 1) AND b = 2, which may not be what you meant. Parenthesize whenever NOT meets AND or OR.

Misconceptions about WHERE with three conditions

The biggest myth is that condition order in the text controls execution order. It does not; the optimizer reorders freely unless you force a plan. Writing the most selective condition first does nothing for performance in modern engines.

The second myth is that AND and OR are interchangeable for filtering. They are not: AND narrows the result set, OR widens it. Three ANDed conditions return at most the smallest single-condition count; three ORed conditions return at least the largest. Confusing the two inflates or collapses results.

The third is that three conditions in the WHERE clause is inherently slow. Slowness comes from non-sargable predicates, missing indexes, and stale statistics, not from the count of conditions. A well-indexed three-condition filter is routine.

A counterintuitive professional caveat: adding an index for a three-condition query can hurt. Each index adds write cost and storage. On a write-heavy table, a composite index created for one reporting query can degrade insert throughput measurably. Index for the queries that matter, not every filter combination.

Three ANDed predicates can never return more rows than the most selective single predicate. OR across three columns usually defeats single-index seeks unless each branch is indexable. A NULL in any ANDed term removes the row; test NULL explicitly. Implicit type conversion can silently disable an index. Stale optimizer statistics flip good plans into bad ones after bulk loads. A composite index helps only when its leading columns match your filter order.

Comparison of connector strategies

The table's practical reading: parenthesize any OR that sits beside an AND, and expect mixed clauses to plan unpredictably. The two grouped rows behave more predictably than the ungrouped mixed row.

Practical actions for reliable three-condition queries

Write the logic, then verify counts. Start with each condition alone and record its row count. Then test pairs, then all three. If the three-condition count is not between the bounds the connectors imply, you have a precedence or NULL problem.

Parenthesize every OR group. Then read the clause aloud as nested statements. If your spoken version differs from the written parse, fix the parentheses before running anything.

Test NULL behavior explicitly. Run a count of rows where each column IS NULL. If any ANDed column has NULLs, decide whether those rows should pass and add IS NULL tests accordingly.

Check sargability. A predicate wrapped in a function, like WHERE YEAR(created_at) = 2024, usually cannot use the index on created_at. Rewrite as a range: created_at >= '2024-01-01' AND created_at < '2025-01-01'.

Refresh statistics after bulk loads. Stale stats are a leading cause of sudden slowdowns on three-condition queries that ran fine last week.

Use EXPLAIN to confirm the plan. Look for sequential scans where you expected index seeks, and for estimated versus actual row counts that diverge by more than about tenfold.

Limitations and what the clause cannot do

WHERE filters rows before aggregation, so it cannot filter on aggregate results; that belongs in HAVING. Three conditions testing a SUM or COUNT in WHERE will error or mislead. WHERE also runs before window functions, so you cannot filter on a window result in the same clause. These ordering rules are structural, not stylistic.

Optimizer behavior is engine-specific. The same three-condition query plans differently across PostgreSQL, MySQL, SQL Server, and SQLite, and version upgrades change plans. Selectivity estimates depend on statistics quality, which depends on autovacuum or manual updates. No single rule covers every engine, so verify on your system.

Finally, syntax correctness does not guarantee semantic correctness. A query can run without error and still return the wrong rows because of precedence, NULLs, or boundary choices. The mechanisms here explain why; only testing against known data confirms what your specific three-condition filter actually returns.