SQL WHERE Clause: The Complete Guide to Filtering Data

Imagine you have a database with a million customers, but you only need the ones in New York who signed up last year. Without the WHERE clause, you'd be drowning in data you don't care about. With it, you can slice through your data with surgical precision.

The WHERE clause is one of the first things every SQL developer learns — and one of the most powerful tools you'll use every single day. In this guide, we'll go beyond the basics and cover everything you need to filter data confidently.

Setup: Our Sample Tables

We'll use two tables throughout this guide:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    age           INT,
    signup_date   DATE,
    is_active     BOOLEAN
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    product       VARCHAR(100),
    amount        DECIMAL(10,2),
    order_date    DATE,
    status        VARCHAR(20)
);

INSERT INTO customers VALUES
(1,  'Alice',   'Johnson', '[e-mail]',   'New York',    32, '2022-03-15', true),
(2,  'Bob',     'Smith',   '[e-mail]',     'Chicago',     27, '2023-07-01', true),
(3,  'Carol',   'Davis',   '[e-mail]',   'New York',    45, '2021-11-20', false),
(4,  'David',   'Wilson',  '[e-mail]',   'Los Angeles', 31, '2023-01-10', true),
(5,  'Eva',     'Martinez','[e-mail]',     'Chicago',     29, '2022-09-05', true),
(6,  'Frank',   'Brown',   '[e-mail]',   'New York',    52, '2020-06-12', false),
(7,  'Grace',   'Lee',     '[e-mail]',   'Houston',     24, '2023-11-03', true),
(8,  'Henry',   'Taylor',  '[e-mail]',   'Los Angeles', 38, '2022-04-22', true);

---

Basic Comparisons

The WHERE clause uses comparison operators to filter rows. Here are all the ones you'll regularly use:

Operator	Meaning
=	Equal to
<> or !=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

Examples

Find all active customers:

SELECT first_name, last_name, city
FROM customers
WHERE is_active = true;

Result: Alice, Bob, David, Eva, Grace, Henry

Find customers aged 30 or older:

SELECT first_name, age, city
FROM customers
WHERE age >= 30;

Result:
| first_name | age | city |
|---|---|---|
| Alice | 32 | New York |
| Carol | 45 | New York |
| David | 31 | Los Angeles |
| Frank | 52 | New York |
| Henry | 38 | Los Angeles |

---

Combining Conditions: AND, OR, NOT

Real-world queries almost always involve multiple conditions.

AND — both conditions must be true

Active customers in New York:

SELECT first_name, last_name
FROM customers
WHERE city = 'New York'
  AND is_active = true;

Result: Alice Johnson

OR — at least one condition must be true

Customers in New York OR Chicago:

SELECT first_name, city
FROM customers
WHERE city = 'New York'
   OR city = 'Chicago';

Result: Alice (New York), Bob (Chicago), Carol (New York), Eva (Chicago), Frank (New York)

NOT — negate a condition

Customers NOT from New York:

SELECT first_name, city
FROM customers
WHERE NOT city = 'New York';

Combining AND and OR (use parentheses!)

Find active customers who are either from New York or under 30:

-- Without parentheses — this may not do what you expect!
SELECT first_name, age, city
FROM customers
WHERE is_active = true AND city = 'New York' OR age < 30;

-- With parentheses — clear and correct
SELECT first_name, age, city
FROM customers
WHERE is_active = true
  AND (city = 'New York' OR age < 30);

The second version is explicit: it finds active customers where either city is New York or age is under 30. AND binds tighter than OR in SQL, just like multiplication binds tighter than addition in math — parentheses make your intent unmistakable.

---

IN — Match Any Value in a List

Instead of chaining multiple OR conditions, use IN:

-- Verbose version
SELECT first_name, city
FROM customers
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Houston';

-- Clean version with IN
SELECT first_name, city
FROM customers
WHERE city IN ('New York', 'Chicago', 'Houston');

Both return the same result, but IN is much cleaner. You can also negate it:

-- Customers NOT from these cities
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');

Result: David (Los Angeles), Grace (Houston), Henry (Los Angeles)

---

BETWEEN — Range Filtering

BETWEEN checks if a value falls within an inclusive range. Works with numbers, dates, and strings.

Numeric range

Customers aged 25–35:

SELECT first_name, age
FROM customers
WHERE age BETWEEN 25 AND 35;

Result: Alice (32), Bob (27), David (31), Eva (29)

Note: BETWEEN x AND y is inclusive on both ends — equivalent to >= x AND <= y.

Date range

Customers who signed up in 2022:

SELECT first_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-12-31';

Result: Alice (2022-03-15), Eva (2022-09-05), Henry (2022-04-22)

---

IS NULL / IS NOT NULL

NULL in SQL means "unknown" or "missing data." You can't use = NULL to check for it — you must use IS NULL:

-- This NEVER works (always returns 0 rows)
SELECT * FROM customers WHERE email = NULL;

-- This is correct
SELECT * FROM customers WHERE email IS NULL;

Let's add a customer with a missing email to demonstrate:

INSERT INTO customers VALUES
(9, 'Ian', 'Clark', NULL, 'Boston', 33, '2023-05-10', true);

-- Find customers with no email on file
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;

Result: Ian Clark

-- Find customers who DO have an email
SELECT COUNT(*) AS customers_with_email
FROM customers
WHERE email IS NOT NULL;

---

LIKE — Pattern Matching

LIKE lets you search for patterns in text using two wildcards:

• % — matches any sequence of characters (including none)
• _ — matches exactly one character

Find emails from Gmail:

SELECT first_name, email
FROM customers
WHERE email LIKE '[e-mail]';

Find customers whose first name starts with a vowel (A or E):

SELECT first_name
FROM customers
WHERE first_name LIKE 'A%'
   OR first_name LIKE 'E%';

Result: Alice, Eva

Find customers with a 5-letter first name:

SELECT first_name
FROM customers
WHERE first_name LIKE '_____';

Result: Alice, Grace, Henry, Frank (5 underscores = 5 characters)

Case sensitivity: LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL. In PostgreSQL, use ILIKE for case-insensitive matching.

---

Putting It All Together

Here's a real-world query combining multiple WHERE techniques — find active customers aged 25–40 who live in New York or Chicago, and whose email address we have on file:

SELECT 
    first_name,
    last_name,
    age,
    city,
    email
FROM customers
WHERE is_active = true
  AND age BETWEEN 25 AND 40
  AND city IN ('New York', 'Chicago')
  AND email IS NOT NULL
ORDER BY age ASC;

Result:
| first_name | last_name | age | city | email |
|---|---|---|---|---|
| Bob | Smith | 27 | Chicago | [e-mail] |
| Eva | Martinez | 29 | Chicago | [e-mail] |
| Alice | Johnson | 32 | New York | [e-mail] |

One clean query, perfectly targeted results.

---

Common Mistakes to Avoid

1. Using = NULL instead of IS NULL

-- ❌ Always returns zero rows
WHERE discount_code = NULL

-- ✅ Correct
WHERE discount_code IS NULL

2. Forgetting parentheses with AND/OR

-- ❌ Might not mean what you think
WHERE status = 'active' AND city = 'NY' OR city = 'LA'

-- ✅ Explicit and correct
WHERE status = 'active' AND (city = 'NY' OR city = 'LA')

3. Doing math on the filtered column (kills index performance)

-- ❌ Forces a full table scan — can't use an index on order_date
WHERE YEAR(order_date) = 2023

-- ✅ Uses index on order_date
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

4. Using LIKE with a leading wildcard (also kills index performance)

-- ❌ Can't use index — scans every row
WHERE email LIKE '[e-mail]'

-- ✅ Can use index — only scans rows starting with 'alice'
WHERE email LIKE 'alice%'

---

Key Takeaways

• Use comparison operators (=, <>, <, >, etc.) for basic filtering.
• Combine conditions with AND, OR, and NOT — use parentheses when mixing them.
• IN is cleaner than multiple OR conditions for matching a list of values.
• BETWEEN provides inclusive range filtering for numbers and dates.
• Always use IS NULL or IS NOT NULL — never = NULL.
• LIKE with % and _ enables pattern matching; avoid leading wildcards on large tables.

The WHERE clause is the foundation of meaningful SQL queries. Mastering it means you can always get exactly the data you need — no more, no less.

---

Got a tricky filtering scenario you've run into? Drop it in the comments — I'd love to help work through it!