SQL WHERE Clause

The SQL WHERE clause filters out rows that don't meet a condition. It lets a SELECT return specific records and keeps an UPDATE or DELETE from touching rows it shouldn't. You can build its conditions with comparisons, NULL checks, AND, OR, IN, and BETWEEN.

This article is based on filtering lessons from Learn SQL. If you want to run the queries against a real database, take the full course.

What Is the SQL WHERE Clause?

When making CRUD queries, we need a way to make the instructions we send to the database more specific. SQL accepts a WHERE statement within a query that allows us to be very specific with our instructions. If we were unable to specify the record we wanted to READ, UPDATE, or DELETE, making queries to a database would be very frustrating, and very inefficient.

Say we had over 9000 records in our users table. We often want to look at specific user data within that table without retrieving all the other records in the table. We can use a SELECT statement followed by a WHERE clause to specify which records to retrieve. The SELECT statement stays the same, we just add the WHERE clause to the end of the SELECT. Here's an example:

SELECT name FROM users WHERE power_level >= 9000;

This will select only the name field of any user within the users table WHERE the power_level field is greater than or equal to 9000. It doesn't change the table.

The same filtering syntax works with UPDATE and DELETE:

UPDATE users
SET is_admin = false
WHERE account_status = 'closed';

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

Without WHERE, an UPDATE or DELETE affects every eligible row. Test the condition with a SELECT before running a destructive query.

WHERE Comparison Operators

All of the following operators are supported in SQL. The = is the main one to watch out for – it's not == like in many other languages! SQLite does allow for ==, but it's not a good habit to get into, as other dialects of SQL will not recognize == as valid syntax.

SELECT name, age
FROM users
WHERE age >= 18;

Some databases accept != for not equal, but the SQL standard spelling is <>. Prefer <> when the query needs to be portable.

Comparisons depend on data types and database rules. Numbers compare numerically. A column's collation controls details of text comparison, including sort order and often case sensitivity. The SQL LIKE operator handles pattern matching.

Filtering NULL Values With WHERE

NULL represents an unknown or missing value. Don't compare it with = or <>:

WHERE first_name = NULL

That condition doesn't evaluate to true, even when first_name is NULL. Instead, you can use a WHERE clause to filter values by whether or not they're NULL with IS NULL and IS NOT NULL:

SELECT name FROM users WHERE first_name IS NULL;

SELECT name FROM users WHERE first_name IS NOT NULL;

SQL conditions use three-valued logic: true, false, or unknown. Most comparisons involving NULL produce unknown, and WHERE keeps only true rows. This rule also explains the NOT IN trap covered below.

Combining Conditions With AND and OR

We often need to use multiple conditions to retrieve the exact information we want. We can begin to structure much more complex queries by using multiple conditions together to narrow down the search results of our query.

The logical AND operator can be used to narrow down our result sets even more!

SELECT product_name, quantity, shipment_status
FROM products
WHERE shipment_status = 'pending'
  AND quantity BETWEEN 0 and 10;

This only retrieves records where both the shipment_status is "pending" AND the quantity is between 0 and 10.

As you've probably guessed, if the logical AND operator is supported, the OR operator is probably supported as well.

SELECT product_name, quantity, shipment_status
FROM products
WHERE shipment_status = 'out of stock'
  OR quantity BETWEEN 10 and 100;

This query retrieves records where either the shipment_status condition or the quantity condition is met.

You can combine as many conditions as the query needs, but long ungrouped expressions are hard to verify. Put each condition on its own line once a filter has multiple parts.

AND and OR Evaluation Order

You can group logical operations with parentheses to specify the order of operations:

(this AND that) OR the_other

SQL evaluates AND before OR. Without parentheses, this condition includes every US user but only Canadian users under 18:

WHERE country_code = 'US'
   OR country_code = 'CA'
  AND age < 18

If the age rule should apply to both countries, group the alternatives:

SELECT name, age, country_code
FROM users
WHERE (country_code = 'US' OR country_code = 'CA')
  AND age < 18;

Here, the parentheses change the query's meaning, not just its readability. Even when the default operator precedence already produces the intended result, parentheses make mixed AND and OR conditions easier to review.

Using IN and NOT IN

Another variation to the WHERE clause we can utilize is the IN operator. IN returns true or false if the first operand matches any of the values in the second operand. The IN operator is a shorthand for multiple OR conditions.

These two queries are equivalent:

SELECT product_name, shipment_status
FROM products
WHERE shipment_status IN ('shipped', 'preparing', 'out of stock');

SELECT product_name, shipment_status
FROM products
WHERE shipment_status = 'shipped'
  OR shipment_status = 'preparing'
  OR shipment_status = 'out of stock';

Use separate OR branches when the alternatives test different columns.

NOT IN excludes listed values:

SELECT name, country_code
FROM users
WHERE country_code NOT IN ('US', 'CA', 'MX');

Hopefully, you're starting to see how querying specific data using fine-tuned SQL clauses helps reveal important insights! The larger a table becomes, the harder it becomes to analyze without proper queries.

The NOT IN NULL trap

If the list contains NULL, a nonmatching value produces unknown instead of true:

SELECT name
FROM users
WHERE country_code NOT IN ('US', 'CA', NULL);

That query returns no rows: listed values are false, while every other comparison is unknown. Remove NULL from a literal list. Add OR country_code IS NULL if missing country codes should remain. A NOT IN subquery needs the same kind of care; the SQL subqueries guide covers that case and the NOT EXISTS alternative.

SQL BETWEEN Is Inclusive

We can check if values are between two numbers using the WHERE clause in an intuitive way! The WHERE clause doesn't always have to be used to specify specific IDs or values. We can also use it to help narrow down our result set. Here's an example:

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 60000;

This query returns all the employees name and salary fields for any rows where the salary is BETWEEN 30,000 and 60,000 inclusively! The condition is equivalent to:

WHERE salary >= 30000 AND salary <= 60000

We can also query results that are NOT BETWEEN two specified values.

SELECT product_name, quantity
FROM products
WHERE quantity NOT BETWEEN 20 AND 100;

This query returns all the product names and quantities where the quantity was not between 20 and 100 (meaning it excludes 20 and 100). We can use conditionals to make the results of our query as specific as we need them to be.

BETWEEN works well for closed ranges, which include both endpoints. Put the lower bound first; SQL won't reorder the bounds for you.

BETWEEN With Dates and Timestamps

BETWEEN is also inclusive for dates and timestamps. That can make a reasonable-looking timestamp filter miss most of the final day:

WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31'

When the database converts '2026-07-31' to a timestamp at midnight, rows later on July 31 fall outside the range. For timestamps, prefer a half-open interval: include the start and exclude the end.

WHERE created_at >= '2026-07-01'
  AND created_at < '2026-08-01'

This works regardless of timestamp precision. Use parameters with the same type as the column, and convert business dates into the intended time zone before constructing the boundaries. Timestamp and time-zone rules differ by database and column type.

CASE With WHERE Conditions

CASE returns a value based on conditions. It doesn't replace WHERE: WHERE decides which rows survive, while CASE calculates a result for each surviving row.

Say CashPal, a payments app, has launched two discount programs:

1. All users older than 55 will qualify for a senior discount.
2. Users from Canada (country_code 'CA') qualify for a Canada Day discount.

Users who qualify for either discount will get 10% off. A CASE expression can add a discount_percent column with an integer value of 10 or 0, depending on whether the user matches any discount condition:

SELECT name,
  CASE
    WHEN age > 55 OR country_code = 'CA' THEN 10
    ELSE 0
  END AS discount_percent
FROM users;

Every user remains in the result, but qualifying users get a discount_percent of 10. To return only those users, put the condition in WHERE instead:

SELECT name
FROM users
WHERE age > 55 OR country_code = 'CA';

Use standard CASE when portability matters. Database-specific conditional functions may be shorter, but they make moving the query between PostgreSQL, MySQL, SQLite, and other systems harder.

WHERE vs. HAVING vs. ON

All three clauses can contain conditions, but they filter at different stages:

Use HAVING for conditions on grouped results:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 500;

WHERE removes unpaid orders before aggregation. HAVING then removes customer groups whose totals aren't above 500. See SQL aggregate functions for the full grouping order.

In a join, ON decides which rows match. A later WHERE filters the joined result. The distinction matters most with outer joins because filtering a right-side column that can be NULL may remove unmatched left rows. The SQL joins guide shows the LEFT JOIN filtering trap in detail.