Multiple Conditions — AND, OR, NOT

Operator precedence determines the order in which SQL evaluates logical operators when multiple operators appear in the same WHERE clause. In SQL, NOT has the highest precedence, AND has the second highest, and OR has the lowest. This means SQL evaluates NOT conditions first, then AND conditions, and finally OR conditions — regardless of the order they appear in the query text.

This matters because mixing AND and OR without parentheses can produce results that differ from what was intended. For example: WHERE tier = 'Gold' OR tier = 'Platinum' AND city = 'Bangalore' is evaluated as WHERE tier = 'Gold' OR (tier = 'Platinum' AND city = 'Bangalore') — because AND binds before OR. This returns all Gold customers from any city plus Platinum customers from Bangalore only, rather than Gold and Platinum customers from Bangalore as the author likely intended.

The correct approach is always to use parentheses when combining AND and OR: WHERE (tier = 'Gold' OR tier = 'Platinum') AND city = 'Bangalore'. Parentheses override precedence completely — expressions inside parentheses are always evaluated first. Even when precedence would naturally produce the correct result, writing explicit parentheses makes intent clear to anyone reading the query and prevents bugs when conditions are added or modified later.

AND returns TRUE only when both conditions are true — it narrows results by requiring all conditions to be satisfied simultaneously. Every additional AND condition makes the filter more restrictive. OR returns TRUE when at least one condition is true — it broadens results by accepting rows that satisfy any of the conditions. Every additional OR condition makes the filter more permissive.

Use AND when you need rows that satisfy multiple criteria simultaneously: orders that are both delivered AND paid by UPI AND above ₹1,000. All three characteristics must be present in the same row. Use OR when you need rows that match any of several alternative criteria: customers from Bangalore OR Hyderabad OR Mumbai. A customer matching any one city qualifies.

A practical test: if you read the question with "AND" and "OR" literally, the answer is usually correct. "Give me customers from Bangalore AND with Gold tier" — AND. "Give me customers from Bangalore OR Hyderabad" — OR. "Give me Gold or Platinum customers from Bangalore" — the "or" applies to tier, so OR for tier, then AND for the city: WHERE (tier = 'Gold' OR tier = 'Platinum') AND city = 'Bangalore'.

NOT reverses the logical result of a condition. A condition that evaluates to TRUE becomes FALSE, and a condition that evaluates to FALSE becomes TRUE. NOT NULL, however, is still NULL — the unknown reversed is still unknown. This is the fundamental limitation of NOT with nullable columns.

The practical consequence: WHERE NOT city = 'Bangalore' returns rows where city is any value other than 'Bangalore' but does NOT return rows where city IS NULL. Rows with a null city silently disappear from the results. If you want to include null values in a NOT condition, you must explicitly add them: WHERE (city <> 'Bangalore' OR city IS NULL).

NOT is most commonly used in two forms: NOT LIKE (does not match a pattern), NOT IN (value not in a list), and NOT EXISTS (no matching rows in a subquery). The NOT IN form has a particularly dangerous NULL interaction — if the IN list contains even one NULL value, NOT IN returns zero rows for the entire query, which is almost never what was intended. This is why NOT EXISTS is often preferred over NOT IN for correlated subqueries. You will learn this in Module 38.

Due to operator precedence (AND before OR), this query is evaluated as: WHERE status = 'Delivered' OR (status = 'Returned' AND amount > 1000). It returns two groups of rows: all rows where status is 'Delivered' regardless of amount (every delivered order, whether ₹10 or ₹100,000), plus rows where status is 'Returned' AND amount is greater than 1,000.

This is almost certainly not what was intended. The author likely wanted all Delivered or Returned orders with amount above 1,000. The correct query uses parentheses: WHERE (status = 'Delivered' OR status = 'Returned') AND amount > 1000. The parentheses force the OR to be evaluated first, grouping the two status values, and then AND applies the amount threshold to the combined group.

This type of bug is particularly insidious because the query runs without error and returns results — just wrong results. The only way to catch it is to understand precedence rules and verify results against expected counts. Always run a SELECT COUNT(*) to verify the row count makes sense before using the results for any decision. If Delivered orders are 18 of your 30 orders, a query returning 22 rows for "delivered or returned above 1000" should immediately prompt a precedence check.

There are several correct approaches with different trade-offs. The most readable approach uses NOT with an OR group: WHERE NOT (city = 'Bangalore' OR city = 'Hyderabad' OR city = 'Mumbai'). This is evaluated as: exclude rows where city is any of the three. By De Morgan's law, this is equivalent to: WHERE city <> 'Bangalore' AND city <> 'Hyderabad' AND city <> 'Mumbai'.

A more concise and equally readable approach uses NOT IN: WHERE city NOT IN ('Bangalore', 'Hyderabad', 'Mumbai'). This is cleaner for more than two or three values. Both approaches are semantically identical and produce the same results.

The critical caveat: if the city column can contain NULL values, neither NOT with OR nor NOT IN will include rows where city is NULL — they will silently exclude them. If you want to include customers with an unknown city alongside those from non-top-three cities, add an explicit NULL check: WHERE (city NOT IN ('Bangalore', 'Hyderabad', 'Mumbai') OR city IS NULL). In the FreshMart customers table, city is defined NOT NULL, so this is not an issue — but in any table where nullable city is possible, the NULL case must be handled explicitly.

Cause: Operator precedence bug — AND evaluates before OR. The query is actually evaluated as: WHERE city = 'Bangalore' OR (city = 'Hyderabad' AND loyalty_tier = 'Gold'). This returns ALL Bangalore customers regardless of tier, plus only Gold customers from Hyderabad. If the intention was Gold customers from both cities, the result includes non-Gold Bangalore customers which should not be there.

Fix: Always use parentheses when combining AND and OR: WHERE (city = 'Bangalore' OR city = 'Hyderabad') AND loyalty_tier = 'Gold'. To debug, run SELECT COUNT(*) with each version and compare to expected counts. If your table has 8 Bangalore customers and 4 Hyderabad-Gold customers, the buggy query returns 12. The fixed query returns only the Bangalore-Gold plus Hyderabad-Gold count.

Cause: AND and OR must connect two complete conditions — they cannot appear at the start of a WHERE clause with nothing before them, and they cannot appear between a condition and nothing. This error happens when a condition is accidentally deleted leaving only the AND/OR connector, or when copying and pasting conditions in the wrong order.

Fix: Ensure AND and OR always connect exactly two complete conditions. Each side of AND/OR must be a valid condition: column operator value, or a parenthesised group of conditions. Check that you have not accidentally deleted the first condition in the pair. In editors that highlight syntax, AND/OR at the start of a clause (with nothing before it) is visually obvious as a red highlight.

Cause: NOT IN returns zero rows when the list contains even one NULL value. If order_status can be NULL and any row has a NULL status, NOT IN internally evaluates to: status <> 'Cancelled' AND status <> 'Returned' AND status <> NULL. Since any comparison with NULL returns NULL (not TRUE), the entire condition becomes NULL for every row — and the WHERE clause discards all NULL results. Even if your list has no explicit NULLs, if the column itself has NULL values, NOT IN behaves unexpectedly.

Fix: Use NOT EXISTS or an explicit exclusion with IS NOT NULL: WHERE order_status IS NOT NULL AND order_status NOT IN ('Cancelled', 'Returned'). Or rewrite using NOT and OR: WHERE NOT (order_status = 'Cancelled' OR order_status = 'Returned') AND order_status IS NOT NULL. In production, always check whether the column can contain NULLs before using NOT IN — SELECT COUNT(*) FROM orders WHERE order_status IS NULL; if this returns any rows, NOT IN is dangerous.

Cause: Applying a function to the column side of OR conditions (LOWER(city)) prevents the database from using an index on the city column. The index stores raw city values ('Bangalore', 'Hyderabad') — not their lowercase equivalents. When LOWER() is applied, the database cannot use the index and must scan every row, applying LOWER() to each one. On a table with millions of rows, this causes a full table scan that is orders of magnitude slower than an index lookup.

Fix: Two approaches. First, standardise data at insertion time — store all city values in consistent case ('Bangalore' not 'bangalore') and use case-sensitive comparison: WHERE city = 'Bangalore' OR city = 'Hyderabad'. This allows index usage. Second, if consistent casing cannot be guaranteed, create a functional index on LOWER(city): CREATE INDEX idx_customers_city_lower ON customers (LOWER(city)); — then WHERE LOWER(city) = 'bangalore' OR LOWER(city) = 'hyderabad' can use this index. For performance-critical queries, always check whether index usage is possible with EXPLAIN ANALYZE (covered in Module 57).

Cause: NOT condition propagates NULL. Rows where city IS NULL evaluate NOT NULL = NULL (not FALSE, and not TRUE) — and the WHERE clause discards NULL results. If you intended WHERE NOT city = 'Bangalore' to mean 'all customers not specifically in Bangalore — including those with no city recorded,' the query silently excludes the NULL-city rows without any error or warning.

Fix: Explicitly include NULL rows: WHERE city <> 'Bangalore' OR city IS NULL. The OR city IS NULL clause captures rows that were being silently excluded. This pattern — adding OR column IS NULL — is the standard fix whenever you need a NOT condition to include rows with null values. Run SELECT COUNT(*) FROM customers WHERE city IS NULL before and after to confirm whether NULL rows are present and whether your fix captures them.

The FreshMart finance team needs two separate reports. Report 1: All orders from store ST001 OR ST008 that were delivered AND paid by either Card or NetBanking — sorted by total_amount descending. Report 2: All orders that are NOT delivered (any other status) AND have a total_amount above ₹400. Write both queries.