SQL WHERE: AND/OR Precedence Bug Sent 40k Wrong Customers

Every real-world database holds thousands, sometimes millions of rows. A table of online orders might have five million records going back a decade. Without a way to pinpoint just the rows you care about, querying a database would be like searching for a specific email by reading your entire inbox from the beginning. The WHERE clause is the most fundamental filtering tool in SQL — and it's why databases are actually useful in practice, not just in theory.

Before WHERE existed as a concept, you'd have to pull every row into your application and filter it in code. That means your server drags across the network, your app chews through memory, and your users wait. WHERE pushes the filtering down to the database engine itself — the place best equipped to do it fast, using indexes. It solves the problem of unnecessary data transfer and processing at its source.

By the end of this article you'll know how to write WHERE clauses from scratch, combine multiple conditions using AND, OR, and NOT, use powerful operators like BETWEEN, LIKE, and IN, and avoid the three beginner mistakes that silently break your queries. You'll also walk away with the answers to the interview questions that trip up even developers who've been writing SQL for a year.

What the WHERE Clause Actually Does (And Why It Belongs After FROM)

Every SQL SELECT statement follows a logical order: you tell the database WHAT columns you want (SELECT), then WHERE to look for rows (FROM), and then WHICH rows to keep (WHERE). The WHERE clause acts as a gatekeeper — the database evaluates every row in the table against your condition, and only the rows that pass get returned.

Think of it like a bouncer at a club checking IDs. Every single person in the queue gets checked. If your condition says 'age >= 21', only people who meet that rule get in. Everyone else is turned away quietly — they don't throw an error, they just don't appear in your results.

The clause uses standard comparison operators you already know from maths: = (equals), != or <> (not equals), > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to). You attach the condition to a column name, and the database tests each row against it.

One thing that trips beginners up: WHERE is evaluated BEFORE SELECT. The database finds matching rows first, then decides which columns to show. This matters when you start writing more advanced queries.

-- Imagine a small e-commerce database.
-- This is our 'orders' table with real-looking data.

-- First, let's see ALL orders (no filter) so we understand what we're working with
SELECT order_id, customer_name, country, total_amount
FROM orders;

-- Output above shows 6 rows. Now let's FILTER:
-- We only want orders from customers in the USA.
-- The WHERE clause checks 'country' for every row.
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE country = 'USA';  -- Only rows where country equals 'USA' pass through

-- Now filter by a number — orders over $300
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE total_amount > 300;  -- '>' means strictly greater than, so 300.00 itself is excluded

-- Not equal to — find everyone NOT from Canada
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE country != 'Canada';  -- <> works identically: WHERE country <> 'Canada'

Combining Conditions with AND, OR, and NOT — The Logic Operators

One condition is useful. Multiple conditions are powerful. SQL gives you three logic operators to combine conditions: AND, OR, and NOT.

AND means BOTH conditions must be true. Think of it as a strict filter — like finding customers who live in the USA AND spent more than $300. Both boxes must be ticked.

OR means AT LEAST ONE condition must be true. Think of a wider net — customers from the USA OR customers from Canada. Either one qualifies.

NOT flips a condition on its head. WHERE NOT country = 'USA' is the same as WHERE country != 'USA'. It's most useful with operators like IN and LIKE, which we'll cover shortly.

Here's the critical thing beginners miss: AND has higher precedence than OR, just like multiplication beats addition in maths. So WHERE country = 'USA' OR country = 'Canada' AND total_amount > 300 does NOT mean what you think. The AND runs first, binding only the Canada condition to the amount check. Always use parentheses to make your logic explicit and readable.

-- AND: both conditions must be true
-- Find USA customers who spent more than $300
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE country = 'USA'
  AND total_amount > 300;  -- Both must match — the row clears TWO checkpoints

-- OR: at least one condition must be true
-- Find anyone from the USA OR from India
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE country = 'USA'
   OR country = 'India';  -- Passes if EITHER condition is true

-- NOT: inverts the condition
-- Find every order that is NOT from the USA
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE NOT country = 'USA';  -- Equivalent to: WHERE country != 'USA'

-- MIXING AND + OR — always use parentheses to be safe!
-- Find: (USA customers over $300) OR (any Indian customer)
SELECT order_id, customer_name, country, total_amount
FROM orders
WHERE (country = 'USA' AND total_amount > 300)  -- Group 1 with parentheses
   OR (country = 'India');                       -- Group 2 with parentheses

BETWEEN, IN, and LIKE — The Power Operators That Replace Messy Conditions

Once you've got AND, OR, and NOT down, SQL gives you three shortcut operators that make common filtering patterns much cleaner to write and read.

BETWEEN filters rows within a range — it's shorthand for >= and <= combined. WHERE total_amount BETWEEN 100 AND 500 is identical to WHERE total_amount >= 100 AND total_amount <= 500. Both ends are inclusive, meaning 100 and 500 themselves are included.

IN filters against a list of specific values — it's shorthand for chaining multiple OR equals conditions. WHERE country IN ('USA', 'Canada', 'India') beats writing three separate OR conditions, especially when that list grows to ten items.

LIKE is for pattern matching on text. The % symbol means 'any sequence of characters', and the _ symbol means 'exactly one character'. WHERE customer_name LIKE 'A%' finds every name starting with A. WHERE customer_name LIKE '_a%' finds names where the second character is 'a'.

NOT BETWEEN, NOT IN, and NOT LIKE all work as inverses. They're your go-to tools for exclusion filtering.

-- BETWEEN: find orders where total_amount is between $100 and $500 (inclusive)
SELECT order_id, customer_name, total_amount
FROM orders
WHERE total_amount BETWEEN 100 AND 500;  -- Includes rows where amount = 100 or 500 exactly

-- NOT BETWEEN: orders outside that range (under $100 or over $500)
SELECT order_id, customer_name, total_amount
FROM orders
WHERE total_amount NOT BETWEEN 100 AND 500;

-- IN: cleaner alternative to multiple OR conditions
-- Find customers from any of three specific countries
SELECT order_id, customer_name, country
FROM orders
WHERE country IN ('USA', 'Canada', 'India');  -- Much cleaner than 3 separate OR clauses

-- NOT IN: exclude those same countries
SELECT order_id, customer_name, country
FROM orders
WHERE country NOT IN ('USA', 'Canada', 'India');

-- LIKE: pattern matching on text
-- Find all customers whose name STARTS WITH the letter 'A'
SELECT order_id, customer_name
FROM orders
WHERE customer_name LIKE 'A%';  -- % matches zero or more of ANY character

-- Find customers whose name CONTAINS 'son' anywhere
SELECT order_id, customer_name
FROM orders
WHERE customer_name LIKE '%son%';  -- % on both sides = 'son' can appear anywhere

-- _ matches exactly ONE character
-- Find 5-letter names (4 wildcards + 1 specific? No — 5 underscores = exactly 5 chars)
SELECT order_id, customer_name
FROM orders
WHERE customer_name LIKE '_____';  -- Exactly 5 characters in the name

Filtering NULL Values — The Special Case That Breaks Beginners

NULL in SQL doesn't mean zero. It doesn't mean an empty string. It means unknown or absent — there is no value there at all. This distinction matters enormously when filtering.

Here's the trap: you cannot use = to check for NULL. Writing WHERE phone_number = NULL will never return any rows — not because there are none, but because NULL = NULL evaluates to NULL (unknown), not TRUE. The database sees 'I don't know if this equals nothing' and skips the row.

The correct operators are IS NULL and IS NOT NULL. These are the only two ways to reliably filter on the presence or absence of a value.

This also affects AND and OR. If any part of a compound condition involves NULL, the result can be NULL rather than TRUE or FALSE, causing rows to silently vanish from your results. When you're debugging a query that returns fewer rows than expected, checking for unexpected NULLs in your filter columns is always a smart first step.

Real-world scenario: a customers table might have a phone_number column where some customers haven't provided a number. IS NULL helps you find them so you can prompt them to update their profile.

-- Let's say our 'customers' table has an optional 'phone_number' column.
-- Some customers filled it in, some didn't.

-- WRONG WAY: this returns 0 rows, always, even if NULLs exist
SELECT customer_id, customer_name, phone_number
FROM customers
WHERE phone_number = NULL;  -- NULL = NULL is NULL (unknown), not TRUE — NEVER do this

-- RIGHT WAY: use IS NULL to find customers with no phone number
SELECT customer_id, customer_name, phone_number
FROM customers
WHERE phone_number IS NULL;  -- 'IS NULL' is the only reliable NULL check

-- IS NOT NULL: find customers who DID provide a phone number
SELECT customer_id, customer_name, phone_number
FROM customers
WHERE phone_number IS NOT NULL;  -- Only returns rows where a value actually exists

-- Combining NULL checks with other conditions
-- Find customers from the USA who haven't provided a phone number
SELECT customer_id, customer_name, country, phone_number
FROM customers
WHERE country = 'USA'
  AND phone_number IS NULL;  -- Both conditions must be true

The WHERE Clause Execution Order — Why Filtering Before JOIN Saves Hours

Your junior thinks WHERE runs on the final result set. In production, that misconception kills performance. The WHERE clause filters rows before JOINs and GROUP BY execute, not after. This is critical for reducing the amount of data entering memory-bound operations. If you filter after a JOIN, you force the engine to join millions of rows only to discard most. Filter early with WHERE. Push predicates into JOIN conditions when you can—that's called 'predicate pushdown' and it's how senior engineers keep queries sub-second. Why does this matter? Because a query that runs in 200ms with early filtering can blow up to 20 seconds when filters are misplaced. Always put your most selective filter first in WHERE. The optimizer might reorder it, but it's a good habit. Test with EXPLAIN to see the actual execution plan. If you see a full table scan before a filter, you've broken the order.

-- io.thecodeforge
-- Wrong: filter after JOIN causes unnecessary work
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2025-01-01';

-- Right: filter before JOIN reduces row count early
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
   AND o.created_at >= '2025-01-01';

-- Alternative: ctid scan killers
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

Correlated Subqueries in WHERE — The Silent Serial Execution Trap

You've seen it: a WHERE clause with a subquery that runs once per row. That's a correlated subquery, and it's the fastest way to turn a 50ms query into a 5-minute one. The engine executes the outer query first, then for each row, runs the inner subquery. This is serial—no parallelism. Why do juniors write them? Because they look clean. 'SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = ''active'')' is actually fine if the subquery is uncorrelated—the engine caches it. But the moment you reference the outer table inside that subquery, you've created a nested loop where each iteration scans the same rows. Replace it with a JOIN or a window function. In production, one correlated subquery in a WHERE clause can spike CPU to 100% and lock your table for writes. Always test with a small dataset first. If you see 'SubPlan' in the EXPLAIN output, you're doing it wrong. The fix is almost always a JOIN with a DISTINCT or LATERAL join.

-- io.thecodeforge
-- Bad: correlated subquery runs N times
SELECT *
FROM orders o
WHERE o.total > (
    SELECT AVG(total)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

-- Good: single pass with window function
WITH customer_avg AS (
    SELECT customer_id, AVG(total) as avg_total
    FROM orders
    GROUP BY customer_id
)
SELECT o.*
FROM orders o
JOIN customer_avg ca ON o.customer_id = ca.customer_id
WHERE o.total > ca.avg_total;

JSON/JSONB Querying in WHERE Clauses

Modern PostgreSQL databases often store semi-structured data in JSON or JSONB columns. Filtering such data in the WHERE clause requires special operators. The -> operator extracts a JSON object field as JSON, while ->> extracts it as text. For JSONB, the @> operator checks if a JSONB document contains a key/value pair. For example, to find customers whose metadata includes a 'vip' status: SELECT * FROM customers WHERE metadata @> '{"status": "vip"}'. You can also use ? for key existence: WHERE metadata ? 'email_verified'. For nested paths, use #>> to extract by path: WHERE metadata #>> '{address, city}' = 'New York'. These operators are indexable with GIN indexes on JSONB columns, drastically improving performance. Always use JSONB over JSON for queryable columns, as JSONB supports indexing and more operators. Avoid extracting fields in WHERE clauses using functions like json_extract_path_text() as they prevent index usage. Instead, use the native operators. Also beware of NULL handling: a missing key returns NULL, not false, so combine with IS NOT NULL if needed. Proper JSON/JSONB filtering can reduce application-side parsing and enable complex conditions directly in SQL.

-- Find customers with VIP status in JSONB metadata
SELECT * FROM customers
WHERE metadata @> '{"status": "vip"}';

-- Find customers who have email_verified key
SELECT * FROM customers
WHERE metadata ? 'email_verified';

-- Find customers in city 'New York' using path extraction
SELECT * FROM customers
WHERE metadata #>> '{address, city}' = 'New York';

-- Create GIN index for performance
CREATE INDEX idx_customers_metadata ON customers USING GIN (metadata);

LATERAL JOIN for Row-by-Row Filtering

The LATERAL JOIN allows a subquery to reference columns from preceding tables in the FROM clause, enabling row-by-row filtering that is impossible with regular joins. For example, to find the most recent order for each customer: SELECT c., o.order_date FROM customers c LEFT JOIN LATERAL (SELECT order_date FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1) o ON true. The LATERAL subquery executes once per customer row, making it powerful for top-N-per-group queries. It can also be used in WHERE clauses indirectly by filtering the outer query based on LATERAL results. For instance, to get customers whose last order was in 2023: SELECT c. FROM customers c WHERE EXISTS (SELECT 1 FROM LATERAL (SELECT order_date FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1) o WHERE o.order_date >= '2023-01-01'). LATERAL joins are more efficient than correlated subqueries in SELECT because they can use indexes on the inner table. However, they are still row-by-row, so ensure proper indexing on the join key (e.g., orders.customer_id). Use LATERAL when you need to compute a value per row that depends on the outer row, especially for complex aggregations or filtering on derived data.

-- Find customers with their most recent order date
SELECT c.*, o.last_order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_date AS last_order_date
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) o ON true;

-- Filter customers whose last order was in 2023
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM LATERAL (
        SELECT order_date
        FROM orders
        WHERE customer_id = c.id
        ORDER BY order_date DESC
        LIMIT 1
    ) o
    WHERE o.order_date >= '2023-01-01'
);

-- Ensure index on orders.customer_id for performance
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Full-Text Search WHERE Conditions: tsvector, MATCH, CONTAINS

Full-text search in PostgreSQL uses tsvector and tsquery types for efficient text matching. Instead of LIKE '%word%', which cannot use indexes, use to_tsvector('english', column) @@ to_tsquery('english', 'word'). For example, to find articles containing 'database' and 'tutorial': SELECT FROM articles WHERE to_tsvector('english', title) @@ to_tsquery('english', 'database & tutorial'). You can also use plainto_tsquery for simpler input: @@ plainto_tsquery('english', 'database tutorial'). For prefix matching, use : syntax: to_tsquery('english', 'datab:*'). To improve performance, create a GIN index on the tsvector column: CREATE INDEX idx_articles_tsv ON articles USING GIN (to_tsvector('english', title)). Some databases like SQL Server use CONTAINS and FREETEXT, but PostgreSQL uses the @@ operator. For phrase searches, use phraseto_tsquery. Full-text search is language-aware, handling stemming and stop words. Avoid using LIKE for text search in production; always use full-text search with indexes for scalability. Combine with WHERE conditions on other columns for precise filtering.

-- Find articles containing 'database' and 'tutorial'
SELECT * FROM articles
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'database & tutorial');

-- Simple phrase search
SELECT * FROM articles
WHERE to_tsvector('english', title) @@ phraseto_tsquery('english', 'database tutorial');

-- Prefix search
SELECT * FROM articles
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'datab:*');

-- Create GIN index
CREATE INDEX idx_articles_tsv ON articles USING GIN (to_tsvector('english', title));