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