When you query a table, you rarely want every row it contains. Most of the time you want specific rows — customers from a certain city, orders above a certain price, or books written by a particular author. The WHERE clause is the tool SQL gives you to express exactly which rows should be returned.
In this tutorial you will learn how to use WHERE with comparison operators, logical operators, ranges, lists, pattern matching, and NULL checks. You will also see the most common mistakes beginners make and how to avoid them.
Prerequisites
To follow along, you need:
- A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
- A client where you can execute SQL statements (a GUI tool like DBeaver, or a command-line client).
- Basic familiarity with the
SELECTstatement.
If you have not created a sample database yet, the setup script in the next section will produce everything you need.
Sample Database
Throughout this tutorial we use a small online bookstore schema with two tables: authors and books.
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
author_name VARCHAR(100) NOT NULL,
country VARCHAR(50)
);
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INTEGER NOT NULL,
category VARCHAR(50),
price DECIMAL(8, 2) NOT NULL,
stock INTEGER NOT NULL,
published_on DATE,
CONSTRAINT fk_books_author
FOREIGN KEY (author_id) REFERENCES authors (author_id)
);
INSERT INTO authors (author_id, author_name, country) VALUES
(1, 'Jane Austen', 'United Kingdom'),
(2, 'Haruki Murakami', 'Japan'),
(3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
(4, 'Gabriel Garcia Marquez', 'Colombia');
INSERT INTO books (book_id, title, author_id, category, price, stock, published_on) VALUES
(1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
(2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
(3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
(4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
(5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
(6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
(7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25, '1967-05-30'),
(8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05'),
(9, 'Unknown Title', 2, NULL, 13.00, 4, NULL);
The final row uses NULL for both category and published_on on purpose — we will use it to demonstrate how WHERE handles missing values.
Basic Syntax
The WHERE clause appears after FROM and before clauses like ORDER BY or GROUP BY.
SELECT column_list
FROM table_name
WHERE condition;
conditionis a boolean expression evaluated once per row.- A row is included in the result only when the condition evaluates to true.
- Rows that evaluate to false or unknown (which is what
NULLcomparisons return) are excluded.
Practical Example
Suppose the bookstore manager wants to see every book that costs more than 15.00.
SELECT
book_id,
title,
price
FROM books
WHERE price > 15.00;
Reading the query in logical order:
FROM books— start with every row in thebookstable.WHERE price > 15.00— keep only rows where the price is greater than 15.00.SELECT book_id, title, price— return these three columns.
Expected Result
| book_id | title | price |
|---|---|---|
| 3 | Norwegian Wood | 15.75 |
| 4 | Kafka on the Shore | 18.20 |
| 5 | Half of a Yellow Sun | 16.00 |
| 7 | One Hundred Years of Solitude | 22.00 |
| 8 | Love in the Time of Cholera | 19.99 |
Additional Examples
1. Comparison Operators
SQL supports the standard set of comparison operators.
| Operator | Meaning |
|---|---|
= |
Equal to |
<> or != |
Not equal to |
<, > |
Less than / greater than |
<=, >= |
Less than or equal / etc. |
SELECT title, category
FROM books
WHERE category = 'Classic';
Returns all books whose category is exactly 'Classic'. String comparisons are case-sensitive in some databases (for example, PostgreSQL) and case-insensitive in others (for example, SQL Server with default collation). Consult your database documentation if this matters for your data.
2. Combining Conditions with AND, OR, and NOT
You can combine multiple conditions to express more precise rules.
SELECT title, price, stock
FROM books
WHERE category = 'Classic'
AND stock > 0;
“Return classic books that are currently in stock.”
Use parentheses when mixing AND and OR — AND has higher precedence than OR, and forgetting this is a common source of bugs.
SELECT title, category, price
FROM books
WHERE (category = 'Classic' OR category = 'Fiction')
AND price < 20.00;
3. Ranges with BETWEEN
BETWEEN is a readable shortcut for a closed range. Both endpoints are included.
SELECT title, price
FROM books
WHERE price BETWEEN 12.00 AND 16.00;
This is equivalent to:
SELECT title, price
FROM books
WHERE price >= 12.00
AND price <= 16.00;
4. Lists with IN
Use IN when you want to match any value from a fixed list.
SELECT title, category
FROM books
WHERE category IN ('Classic', 'Fiction');
This is much cleaner than chaining OR conditions.
5. Pattern Matching with LIKE
LIKE matches strings against a pattern. The two standard wildcards are:
%— matches any sequence of characters (including none)._— matches exactly one character.
SELECT title
FROM books
WHERE title LIKE '%Love%';
Returns any title containing the substring Love.
SELECT author_name
FROM authors
WHERE author_name LIKE 'J%';
Returns authors whose name starts with J.
Case sensitivity of LIKE varies between databases. PostgreSQL provides ILIKE for case-insensitive matching; other systems rely on the column’s collation.
6. Handling NULL
NULL is not equal to anything — not even to another NULL. Comparisons involving NULL return unknown, and rows with an unknown result are excluded by WHERE.
To test for missing values, use IS NULL or IS NOT NULL.
SELECT title, published_on
FROM books
WHERE published_on IS NULL;
To exclude rows with a missing category:
SELECT title, category
FROM books
WHERE category IS NOT NULL;
7. Filtering with Dates
Date comparisons work like number comparisons, but the literal format depends on the database.
SELECT title, published_on
FROM books
WHERE published_on >= DATE '2000-01-01';
The DATE '...' prefix is the ANSI-standard form and is accepted by PostgreSQL, MySQL 8+, MariaDB, and SQLite. SQL Server and Oracle Database accept the plain string '2000-01-01' in most contexts as well.
8. Using WHERE in Application Code
When the filter value comes from user input or another variable, use a parameterized query rather than string concatenation. This prevents SQL injection and lets the database reuse the prepared plan.
String sql = """
SELECT book_id, title, price
FROM books
WHERE category = ?
AND price < ?
""";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, category);
statement.setBigDecimal(2, maxPrice);
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
long bookId = resultSet.getLong("book_id");
String title = resultSet.getString("title");
BigDecimal price = resultSet.getBigDecimal("price");
// process the row...
}
}
}
Common Mistakes
Comparing a Value to NULL with =
Incorrect:
SELECT *
FROM books
WHERE category = NULL;
Correct:
SELECT *
FROM books
WHERE category IS NULL;
The first query returns zero rows on every standard-compliant database because category = NULL evaluates to unknown, never true.
Mixing AND and OR Without Parentheses
Incorrect:
SELECT title, category, price
FROM books
WHERE category = 'Classic' OR category = 'Fiction'
AND price < 20.00;
Because AND binds tighter than OR, this is interpreted as “all classic books, plus fiction books cheaper than 20.00”, which is probably not what you want.
Correct:
SELECT title, category, price
FROM books
WHERE (category = 'Classic' OR category = 'Fiction')
AND price < 20.00;
Forgetting WHERE in an UPDATE or DELETE
Before you delete or update rows, verify the filter with a SELECT first.
SELECT *
FROM books
WHERE stock = 0;
Only after confirming the target rows should you run:
DELETE FROM books
WHERE stock = 0;
Warning: Running
UPDATEorDELETEwithout aWHEREclause modifies every row in the table. Practice these statements only in a disposable learning database or after taking a verified backup.
Using <> with NULL
The expression category <> 'Classic' will not return rows where category IS NULL, because comparing NULL to anything yields unknown. If you need those rows too, add an explicit NULL check:
SELECT title, category
FROM books
WHERE category <> 'Classic'
OR category IS NULL;
Database Compatibility
The core WHERE clause and the operators shown here (=, <>, <, >, <=, >=, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, IS NOT NULL) are part of ANSI SQL and work in:
- PostgreSQL
- MySQL
- MariaDB
- SQL Server
- Oracle Database
- SQLite
Notable differences you may encounter:
- Case sensitivity. String equality and
LIKEare case-sensitive in PostgreSQL by default but often case-insensitive in MySQL and SQL Server, depending on collation. - Case-insensitive
LIKE. PostgreSQL offersILIKE. Other systems typically rely on collation or functions such asLOWER(). - Date literals. The
DATE '2000-01-01'form is portable to most systems; Oracle Database also accepts it, while some tools preferTO_DATE('2000-01-01', 'YYYY-MM-DD'). - Boolean values. PostgreSQL has a native
BOOLEANtype; SQL Server and Oracle Database traditionally useBITor numeric flags.
When in doubt, consult the documentation for the specific database and version you are using.
Best Practices
- List the columns you need. Combine a specific
SELECTlist with a preciseWHEREclause instead of scanningSELECT *. - Use parameters, not string concatenation. This prevents SQL injection and improves plan reuse.
- Handle
NULLexplicitly. If a column can beNULL, decide whether those rows should be included and addIS NULL/IS NOT NULLaccordingly. - Parenthesize mixed logical expressions. It makes intent obvious and avoids precedence bugs.
- Verify destructive filters with
SELECTfirst. Never run anUPDATEorDELETEbefore you have seen the rows it will affect. - Prefer readable operators.
BETWEENandINcommunicate intent better than long chains ofAND/OR. - Beware of functions on filtered columns. Wrapping a column in a function (for example,
WHERE LOWER(title) = 'emma') can prevent an index from being used. If this matters, measure with your database’s execution-plan tool before optimizing.
Conclusion
You learned how to use the SQL WHERE clause to filter rows with comparison, logical, range, list, pattern-matching, and NULL conditions. You also saw how operator precedence, three-valued logic, and parameterized queries affect real-world usage. Always verify an UPDATE or DELETE condition with SELECT before modifying data.