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 SELECT statement.

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;

• condition is 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 NULL comparisons 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:

1. FROM books — start with every row in the books table.
2. WHERE price > 15.00 — keep only rows where the price is greater than 15.00.
3. 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 UPDATE or DELETE without a WHERE clause 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 LIKE are case-sensitive in PostgreSQL by default but often case-insensitive in MySQL and SQL Server, depending on collation.
• Case-insensitive LIKE. PostgreSQL offers ILIKE. Other systems typically rely on collation or functions such as LOWER().
• Date literals. The DATE '2000-01-01' form is portable to most systems; Oracle Database also accepts it, while some tools prefer TO_DATE('2000-01-01', 'YYYY-MM-DD').
• Boolean values. PostgreSQL has a native BOOLEAN type; SQL Server and Oracle Database traditionally use BIT or 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 SELECT list with a precise WHERE clause instead of scanning SELECT *.
• Use parameters, not string concatenation. This prevents SQL injection and improves plan reuse.
• Handle NULL explicitly. If a column can be NULL, decide whether those rows should be included and add IS NULL / IS NOT NULL accordingly.
• Parenthesize mixed logical expressions. It makes intent obvious and avoids precedence bugs.
• Verify destructive filters with SELECT first. Never run an UPDATE or DELETE before you have seen the rows it will affect.
• Prefer readable operators. BETWEEN and IN communicate intent better than long chains of AND/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.