Complex WHERE — Combining Conditions

Operator precedence determines the order in which SQL evaluates logical operators when multiple operators appear in the same WHERE clause without explicit parentheses. The order is: parentheses first (highest), then arithmetic operators, then comparison operators (=, <>, LIKE, BETWEEN, IN, IS NULL), then NOT, then AND, and finally OR (lowest). This means AND binds more tightly than OR.

The most common precedence bug arises when mixing AND and OR without parentheses. WHERE tier = 'Gold' OR tier = 'Platinum' AND city = 'Seattle' is evaluated as WHERE tier = 'Gold' OR (tier = 'Platinum' AND city = 'Seattle') — because AND is evaluated before OR. This returns all Gold customers from any city, plus Platinum customers only from Seattle. The intended query — Gold or Platinum customers from Seattle — requires explicit parentheses: WHERE (tier = 'Gold' OR tier = 'Platinum') AND city = 'Seattle'.

The bug is insidious because the query runs without error and returns results that look plausible — it just returns more rows than intended. Without knowing the exact expected count, the bug is invisible. The prevention is simple: always use explicit parentheses when mixing AND and OR in the same WHERE clause. Even when precedence would give the correct result, parentheses make the intent clear and prevent future bugs when conditions are modified.

The systematic process: first, read the business rule in plain English and identify every condition. Each condition becomes a comparison expression. The words "and", "both", "must also" map to AND. The words "or", "either...or", "any of" map to OR. The word "not" maps to NOT.

Second, identify the groupings — which conditions belong together as a unit before being combined with others. In the rule "Gold or Platinum customers from Seattle who joined after 2021," the "from Seattle" and "joined after 2021" apply to the combined (Gold OR Platinum) group — not just to Platinum. The parenthesised structure is: (Gold OR Platinum) AND Seattle AND after 2021.

Third, write the WHERE clause with explicit parentheses matching the English groupings: WHERE (loyalty_tier = 'Gold' OR loyalty_tier = 'Platinum') AND city = 'Seattle' AND joined_date >= '2022-01-01'. Fourth, verify by running COUNT(*) with each condition group in isolation, then combined, and checking that each count makes sense. A quick sanity check: does each row in the result satisfy all the conditions described in the business rule? Sample 5 rows and verify manually. This four-step process — identify, group, write, verify — works for any business rule regardless of complexity.

De Morgan's Laws are logical identities that describe how NOT distributes over AND and OR. Law 1: NOT (A OR B) equals NOT A AND NOT B. Law 2: NOT (A AND B) equals NOT A OR NOT B. These identities mean you can always rewrite a NOT expression over a group by distributing the NOT into each sub-expression and flipping OR to AND (or AND to OR).

In SQL, De Morgan's Laws explain the equivalence between different ways of writing exclusion conditions. NOT IN (a, b, c) is exactly De Morgan applied: it expands to NOT (col = a OR col = b OR col = c), which by De Morgan equals col <> a AND col <> b AND col <> c. This also explains why NOT IN fails when the list contains NULL — the col <> NULL term always evaluates to NULL, making the entire AND expression NULL for every row.

Practical uses in SQL: simplifying double negatives (NOT (NOT condition) = condition), rewriting NOT IN as explicit AND chains for clarity or portability, and understanding why NOT with OR behaves differently than expected. When debugging a NOT condition that excludes more rows than expected, apply De Morgan to rewrite it in positive terms — the positive form is usually easier to reason about. WHERE NOT (status = 'Cancelled' OR status = 'Returned') rewritten as WHERE status <> 'Cancelled' AND status <> 'Returned' makes it immediately clear which values are excluded.

The systematic debugging process starts with establishing the expected count. Before declaring the result wrong, calculate what you expect: "This table has 30 orders. About 18 are delivered. Of those, maybe 8 were paid by UPI. Of those, maybe 4 were above ₹500." If the query returns 12 instead of 4, the filter is too broad.

Next, test each condition group in isolation. Replace the full WHERE with just the first group and run COUNT(*). Then test the second group alone. Then combine them. For AND chains, each added condition should reduce or maintain the count. If adding a condition increases the count, you accidentally wrote OR. For OR chains, each added alternative should increase or maintain the count. If adding an OR alternative reduces the count, you have a precedence issue where AND is stealing from your OR group.

The most effective visual debugging technique: SELECT * from the full query (remove LIMIT) and scan the results for any row that obviously should not be there. A Gold customer from Delhi in a "Platinum Seattle customers" result immediately identifies a missing parenthesis around the loyalty tier OR condition. One wrong row tells you more than a count discrepancy because you can see exactly which condition failed for that specific row. Once identified, fix the condition, rerun, and verify the count matches the expectation before closing the debug session.

A single WHERE clause is appropriate when all conditions apply directly to columns in the available tables, the conditions are logically parallel (all ANDs, or a flat list of ORs), and the number of conditions is manageable — typically fewer than six to eight conditions. Single-level WHERE clauses with good formatting remain readable and maintainable up to this complexity.

A subquery or CTE (WITH clause) is better when: you need to filter on the result of an aggregation (WHERE total_orders > 5 — requires counting first), the filter depends on a derived value that appears in multiple conditions (defining margin_pct once and using it in two conditions avoids repeating the expression), or the logic has two clearly distinct phases (first identify the qualifying customers, then get their orders) that would be clearer as separate steps.

A CTE specifically helps when the same complex condition needs to be reused — define it once in WITH and reference it cleanly in the main query. It also helps when the WHERE conditions are so numerous that even well-formatted parentheses become hard to follow. As a practical rule: if you cannot explain what your WHERE clause does in one sentence, consider whether a CTE would make the steps explicit and self-documenting. The goal is always a query that any analyst on your team can read and immediately understand — formatting, parentheses, and CTE decomposition are all tools toward that goal.

Cause: Operator precedence bug: AND evaluated before OR. WHERE tier = 'Gold' OR tier = 'Platinum' AND city = 'Seattle' evaluates as WHERE tier = 'Gold' OR (tier = 'Platinum' AND city = 'Seattle'). The AND only binds to the Platinum condition, not the Gold condition. Gold customers from any city are returned because there is no city restriction on the Gold side of the OR.

Fix: Add parentheses to group the OR conditions first: WHERE (tier = 'Gold' OR tier = 'Platinum') AND city = 'Seattle'. Run COUNT(*) with and without the parentheses to verify the row count difference matches your expectation. As a general rule: any time you mix AND and OR in the same WHERE clause, add parentheses around every OR group regardless of whether precedence would technically give the correct result.

Cause: You accidentally wrote OR when you meant AND, or you have a parenthesisation error where an AND is inside an OR group that makes it less restrictive than intended. For example: WHERE (status = 'Delivered' AND amount > 500) OR store_id = 'ST001' — removing the AND amount > 500 condition does not change the OR group's store_id = 'ST001' branch, so the overall count depends on what you removed from which branch.

Fix: Test each condition group in isolation to identify which group is behaving unexpectedly. For each group, run SELECT COUNT(*) FROM table WHERE [group] and compare to your mental model. Then verify the combined query's count is consistent with the isolated counts. If adding a condition to an AND chain increases the count, you have an OR hiding inside the chain — check for missing parentheses.

Cause: Poorly formatted WHERE clause with no parentheses, no line breaks, and no comments. When conditions are all on one line with mixed AND/OR, even the original author cannot reliably modify it without introducing a bug. This is a code quality issue, not a SQL error — but it causes real production incidents when someone modifies the condition incorrectly.

Fix: Reformat the WHERE clause: one condition per line, AND/OR at the start of each line, explicit parentheses around every OR group, and a comment above the WHERE block describing the business rule in plain English. If the conditions represent logically distinct segments, consider extracting them into a CTE (WITH clause) with descriptive names. A WHERE clause that takes 2 minutes to write correctly and 2 seconds to read is infinitely better than one that takes 30 seconds to write and 5 minutes to reason about.

Cause: NOT propagates NULL. Rows where order_status IS NULL evaluate as: NOT (NULL = 'Cancelled' OR NULL = 'Returned') = NOT (NULL OR NULL) = NOT NULL = NULL. The WHERE clause discards all NULL results, so rows with NULL status are silently excluded. They are neither Cancelled nor Returned — but they are also not included in the NOT result.

Fix: Add explicit OR IS NULL handling: WHERE (order_status NOT IN ('Cancelled', 'Returned') OR order_status IS NULL). Or use the De Morgan equivalent: WHERE order_status <> 'Cancelled' AND order_status <> 'Returned' OR order_status IS NULL. To diagnose: run SELECT COUNT(*) FROM orders WHERE order_status IS NULL — if this returns any rows, your NOT condition is excluding them silently.

Cause: OR conditions prevent index optimisation across the full condition. With a single condition WHERE status = 'Delivered', the database uses an index on status. With multiple OR conditions across different columns — WHERE status = 'Delivered' OR payment_method = 'UPI' OR store_id = 'ST001' — the database cannot use a single index to satisfy all three alternatives and may resort to a full table scan for each OR branch or the entire condition.

Fix: Several approaches depending on the data size: (1) UNION ALL: run each OR branch as a separate query and combine results — each sub-query can use its own index. SELECT * FROM orders WHERE status = 'Delivered' UNION ALL SELECT * FROM orders WHERE payment_method = 'UPI' UNION ALL SELECT * FROM orders WHERE store_id = 'ST001'. (2) Add a composite index that covers the most common combination. (3) Analyse with EXPLAIN ANALYZE to confirm which conditions are causing the scan and whether indexes exist on those columns. OR-based queries are inherently harder to optimise than AND-based queries — the query optimiser has fewer options.

The FreshCart operations director needs a priority order list with three tiers. Write a single query that returns orders matching ANY of these three conditions: (1) High-priority: Cancelled or Returned orders above ₹800 from stores ST001, ST005, or ST009. (2) Medium-priority: Processing orders older than order_id 1010 with UPI payment. (3) Low-priority: Delivered orders in January 2024 with total above ₹1,200. Show order_id, store_id, order_date, order_status, payment_method, total_amount, and a priority column using CASE WHEN. Sort by priority ascending then total_amount descending.