SQL WHERE Clause: Filtering Data with Conditions

Every database table can hold millions of rows, but you almost never need all of them at once. The SQL WHERE clause is the gatekeeper that decides which rows make it into your results and which get left behind. Whether you're pulling up a single customer record or narrowing invoices to a specific date range, WHERE is the keyword you'll reach for more than any other.

In this guide, you'll learn every major way to filter data with WHERE — from simple comparisons to pattern matching and NULL checks — all demonstrated with real queries against the Chinook database.

Table of Contents

• Basic WHERE Syntax
• Comparison Operators
• Combining Conditions with AND and OR
• Filtering with IN and BETWEEN
• Pattern Matching with LIKE
• Handling NULL Values
• Quick Comparison Cheat Sheet
• Common Mistakes to Avoid
• Practice Makes Perfect
• Summary
• Frequently Asked Questions

Basic WHERE Syntax

The WHERE clause comes after the FROM clause and before any ORDER BY or LIMIT. It evaluates a condition for every row and only returns rows where the condition is TRUE.

SELECT FirstName, LastName, Country
FROM customers
WHERE Country = 'Brazil';

This query scans every row in the customers table and returns only those where the Country column exactly equals 'Brazil'.

Try it yourself — Identify Customers Located in Brazil →

Comparison Operators

Equality (=) is just the start. SQL provides a full set of comparison operators for numeric, date, and text columns.

Let's find invoices with a total greater than $10:

SELECT InvoiceId, CustomerId, Total
FROM invoices
WHERE Total > 10
ORDER BY Total DESC
LIMIT 5;

Try it yourself — Find High Value Invoices →

You can also filter with <= to find short tracks (under 3 minutes = 180,000 milliseconds):

SELECT Name, Milliseconds
FROM tracks
WHERE Milliseconds <= 180000
ORDER BY Milliseconds
LIMIT 5;

Try it yourself — Short Music Tracks →

Combining Conditions with AND and OR

Real-world filters often require multiple conditions. Use AND when all conditions must be true, and OR when at least one must be true.

AND — all conditions must match

SELECT FirstName, LastName, City, Country
FROM customers
WHERE Country = 'France'
  AND City = 'Paris';

OR — at least one condition must match

SELECT FirstName, LastName, Country
FROM customers
WHERE Country = 'USA'
   OR Country = 'Canada'
ORDER BY Country, LastName
LIMIT 5;

Try it yourself — Customers in USA or Canada →

Watch the operator precedence. AND binds tighter than OR, so use parentheses to be explicit:

-- Without parentheses: AND evaluates first
SELECT * FROM customers
WHERE Country = 'Brazil' OR Country = 'Argentina' AND City = 'Buenos Aires';

-- With parentheses: much clearer intent
SELECT * FROM customers
WHERE (Country = 'Brazil' OR Country = 'Argentina') AND City <> 'São Paulo';

Once you're comfortable combining conditions with WHERE, the natural next step is grouping and aggregating those filtered rows. Check out our SQL GROUP BY and HAVING guide to learn how.

Filtering with IN and BETWEEN

When you have multiple values to check or a numeric/date range, IN and BETWEEN make your queries cleaner.

IN — match any value in a list

SELECT FirstName, LastName, City
FROM customers
WHERE City IN ('London', 'Paris', 'Berlin', 'Prague')
ORDER BY City;

Try it yourself — European City Customers →

BETWEEN — match a range (inclusive)

SELECT InvoiceId, InvoiceDate, Total
FROM invoices
WHERE Total BETWEEN 5 AND 15
ORDER BY Total DESC
LIMIT 5;

Try it yourself — Moderate Range Invoices →

BETWEEN is inclusive on both ends, so BETWEEN 5 AND 15 is equivalent to Total >= 5 AND Total <= 15.

Pattern Matching with LIKE

Use LIKE to match text patterns. The two wildcards are:

• % — matches zero or more characters
• _ — matches exactly one character

SELECT Name
FROM tracks
WHERE Name LIKE 'The %'
ORDER BY Name
LIMIT 5;

Try it yourself — Find Tracks Starting with The →

Here are common LIKE patterns:

For case-insensitive matching in databases that support it, use ILIKE (PostgreSQL) or apply LOWER() on both sides.

Handling NULL Values

NULL represents a missing or unknown value. You cannot use = or <> to compare with NULL — you must use IS NULL or IS NOT NULL.

SELECT Name, Composer
FROM tracks
WHERE Composer IS NULL
LIMIT 5;

Try it yourself — Find Tracks with Missing Composers →

A common follow-up is combining IS NOT NULL with other conditions:

SELECT Name, Composer
FROM tracks
WHERE Composer IS NOT NULL
  AND Milliseconds > 300000
ORDER BY Milliseconds DESC
LIMIT 5;

If you want to combine WHERE filtering with data from multiple tables, see our SQL JOINs guide for a complete walkthrough.

Quick Comparison Cheat Sheet

Common Mistakes to Avoid

1. Using = to check for NULL

-- Wrong: This returns zero rows, even if NULLs exist
SELECT * FROM tracks WHERE Composer = NULL;

-- Right: Always use IS NULL
SELECT * FROM tracks WHERE Composer IS NULL;

2. Forgetting parentheses with mixed AND / OR

-- Wrong: AND binds first, so this finds all Brazil customers
-- PLUS Argentina customers in Buenos Aires only
SELECT * FROM customers
WHERE Country = 'Brazil' OR Country = 'Argentina' AND City = 'Buenos Aires';

-- Right: Parentheses make your intent clear
SELECT * FROM customers
WHERE (Country = 'Brazil' OR Country = 'Argentina')
  AND City = 'Buenos Aires';

3. Using LIKE without wildcards

-- Pointless: LIKE without % or _ is just an expensive =
SELECT * FROM tracks WHERE Name LIKE 'Bohemian Rhapsody';

-- Better: Use = for exact matches, LIKE for patterns
SELECT * FROM tracks WHERE Name = 'Bohemian Rhapsody';

4. Mixing up BETWEEN boundaries

-- Wrong: The smaller value must come first
SELECT * FROM invoices WHERE Total BETWEEN 15 AND 5;

-- Right: Low value first, high value second
SELECT * FROM invoices WHERE Total BETWEEN 5 AND 15;

5. Comparing different data types without casting

-- Risky: Implicit type conversion can cause unexpected results
SELECT * FROM invoices WHERE InvoiceDate > '2021';

-- Better: Use a full date string for clarity
SELECT * FROM invoices WHERE InvoiceDate > '2021-01-01';

Practice Makes Perfect

The best way to master WHERE is to write real queries. Here are exercises on SQiLs that put every concept from this guide into practice:

Join 500+ SQL learners already practicing on SQiLs — Browse all exercises →

Summary

• The WHERE clause filters rows based on conditions and belongs after FROM.
• Use comparison operators (=, <>, >, <, >=, <=) for exact and range checks.
• Combine conditions with AND (all must be true) and OR (at least one must be true) — always use parentheses for clarity.
• IN simplifies multiple OR checks; BETWEEN handles inclusive ranges.
• LIKE with % and _ wildcards enables flexible pattern matching.
• Always use IS NULL / IS NOT NULL instead of = when checking for missing values.
• Once you filter rows with WHERE, explore GROUP BY and HAVING to aggregate them, or JOINs to combine data from multiple tables.

Ready to put this into practice? Start solving exercises on SQiLs →

Frequently Asked Questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping takes place. HAVING filters after GROUP BY has created groups, so it can reference aggregate functions like COUNT() or SUM(). You cannot use aggregates in a WHERE clause. For a deep dive, read our SQL GROUP BY and HAVING guide.

Can I use WHERE with JOIN?

Absolutely. The WHERE clause works the same way whether your FROM clause references one table or several joined tables. Place your join conditions in the ON clause and your filtering conditions in WHERE. Learn more in our SQL JOINs guide.

Is WHERE case-sensitive?

It depends on your database. In SQLite and MySQL with default collation, string comparisons in WHERE are case-insensitive. In PostgreSQL, they are case-sensitive by default — use ILIKE or LOWER() for case-insensitive matching.

How do I filter by date in SQL?

Use standard comparison operators with date strings in ISO format (YYYY-MM-DD). For example: WHERE InvoiceDate >= '2021-01-01' AND InvoiceDate < '2021-04-01' returns all invoices from Q1 2021. You can also use BETWEEN for inclusive date ranges.