SQL WHERE Clause to Filter Query Rows

So far, every query you have written has returned all rows from a table. But in real applications, you almost never want every single row. You want customers from a specific city, orders placed after a certain date, products within a price range, or users who have not verified their email. This is where the WHERE clause comes in.

The WHERE clause is the gatekeeper of your SQL queries. It evaluates every row in the table against a condition you define and only lets through the rows that pass. It transforms SQL from a tool that dumps entire tables into a tool that answers precise, specific questions about your data.

This guide covers everything you need to know about the WHERE clause: all six comparison operators, how to filter text, numbers, and dates, and the critically important concept of NULL values. Each concept includes practical examples with outputs using the ShopSmart sample database so you can follow along in your own environment.

How the WHERE Clause Works

The WHERE clause goes after the FROM clause and defines a condition that each row must satisfy to be included in the results:

SELECT column1, column2
FROM table_name
WHERE condition;

The database evaluates the condition for every row in the table. If a row satisfies the condition, it appears in the results. If it does not, the row is excluded. Think of it as a filter that only allows matching rows to pass through.

Here is a simple example. To find all products that cost more than $50:

SELECT name, price
FROM products
WHERE price > 50;

Output:

The products table has 10 rows, but only 4 satisfy the condition price > 50. The other 6 rows were evaluated, failed the condition, and were excluded from the results.

Comparison Operators

SQL provides six comparison operators for building WHERE conditions. These operators compare a column's value against a specified value and return either true or false for each row.

Let us explore each one with practical examples.

Equal To ( = )

The = operator finds rows where a column's value exactly matches the specified value. This is the most commonly used comparison operator.

SELECT first_name, last_name, city
FROM customers
WHERE city = 'Chicago';

Output:

Only customers whose city column contains exactly 'Chicago' are returned.

SELECT id, customer_id, total_amount, status
FROM orders
WHERE status = 'completed';

Output:

Not Equal To ( != or <> )

The != operator (or its equivalent <>) finds rows where a column's value does not match the specified value.

SELECT name, price, is_available
FROM products
WHERE is_available != true;

Output:

Only one product is currently unavailable. Every other product passed the is_available != true test and would have been included, but this query specifically finds the ones that are not available.

You can also use <>, which is the ANSI SQL standard syntax for "not equal to":

-- Both queries produce identical results
SELECT id, status FROM orders WHERE status <> 'pending';
SELECT id, status FROM orders WHERE status != 'pending';

Output:

Less Than ( < ) and Greater Than ( > )

These operators compare numerical values (or dates, as we will see later) to find rows that fall below or above a threshold.

-- Products priced below $35
SELECT name, price
FROM products
WHERE price < 35;

Output:

-- Products priced above $100
SELECT name, price
FROM products
WHERE price > 100;

Output:

Note that < and > are exclusive, meaning the boundary value itself is not included. A product priced at exactly $35.00 would not appear in the price < 35 results. A product priced at exactly $100.00 would not appear in the price > 100 results.

Less Than or Equal To ( <= ) and Greater Than or Equal To ( >= )

These operators work like < and > but include the boundary value.

-- Products priced at $45 or less
SELECT name, price
FROM products
WHERE price <= 45;

Output:

Notice that the USB-C Hub at exactly $45.00 is included because we used <= instead of <.

-- Products with 100 or more units in stock
SELECT name, stock_quantity
FROM products
WHERE stock_quantity >= 100;

Output:

The Yoga Mat Premium with exactly 100 units is included because >= means "greater than or equal to."

Choosing Between Exclusive and Inclusive Operators

The difference between > and >= (or < and <=) is a single boundary value, but choosing the wrong one can cause subtle bugs.

-- Scenario: Find products that cost $45 or more

-- Wrong: Excludes products priced exactly at $45
SELECT name, price FROM products WHERE price > 45;
-- USB-C Hub ($45.00) is MISSING from results

-- Correct: Includes the $45 boundary
SELECT name, price FROM products WHERE price >= 45;
-- USB-C Hub ($45.00) is INCLUDED in results

Always ask yourself: "Should the boundary value be included?" If yes, use >= or <=. If no, use > or <.

Filtering Text Values

When filtering text (string) data, you must wrap the value in single quotes. This is a strict syntax rule in SQL.

SELECT first_name, last_name, email
FROM customers
WHERE last_name = 'Martinez';

Output:

Single Quotes Are Required for Strings

This is a rule that trips up many beginners. All text values in SQL must be enclosed in single quotes ('...'), not double quotes.

-- Correct: Single quotes around text values
SELECT * FROM customers WHERE city = 'New York';

-- Wrong: Double quotes (behavior varies by database)
SELECT * FROM customers WHERE city = "New York";

Text Comparisons and Case Sensitivity

How text comparisons handle uppercase and lowercase depends on your database:

-- PostgreSQL: Case-sensitive by default
SELECT * FROM customers WHERE city = 'new york';
-- Returns: NO ROWS (stored as 'New York')

SELECT * FROM customers WHERE city = 'New York';
-- Returns: Alice Johnson, David Chen

-- MySQL: Case-insensitive by default (with default collation)
SELECT * FROM customers WHERE city = 'new york';
-- Returns: Alice Johnson, David Chen

To write queries that behave consistently regardless of database, normalize both sides of the comparison using LOWER() or UPPER():

-- Works the same way in every database
SELECT first_name, last_name, city
FROM customers
WHERE LOWER(city) = 'new york';

Output (consistent across all databases):

Comparison Operators with Text

The <, >, <=, and >= operators work with text values too. They compare strings alphabetically (technically, by their character encoding order).

-- Customers whose last name comes before 'Johnson' alphabetically
SELECT first_name, last_name
FROM customers
WHERE last_name < 'Johnson';

Output:

Both "Brown" and "Chen" come before "Johnson" in alphabetical order.

-- Products whose name starts with letters after 'M' alphabetically
SELECT name
FROM products
WHERE name >= 'M';

Output:

While alphabetical comparison is valid SQL, filtering text is more commonly done with LIKE and pattern matching, which you will learn in a future guide.

Filtering by Numbers

Numeric filtering is the most straightforward use of WHERE. You compare a numeric column against a number without any quotes.

-- Exact price match
SELECT name, price
FROM products
WHERE price = 89.99;

Output:

-- Orders with a total exceeding $100
SELECT id, customer_id, total_amount
FROM orders
WHERE total_amount > 100;

Output:

-- Products with low stock (30 or fewer units)
SELECT name, stock_quantity
FROM products
WHERE stock_quantity <= 30;

Output:

Filtering by Dates

Dates in SQL are typically represented as strings in the format 'YYYY-MM-DD' (year-month-day) and enclosed in single quotes. Despite being written as strings, the database understands them as dates and allows meaningful comparisons.

-- Customers who signed up after June 1, 2023
SELECT first_name, last_name, signup_date
FROM customers
WHERE signup_date > '2023-06-01';

Output:

-- Orders placed before February 2024
SELECT id, order_date, total_amount
FROM orders
WHERE order_date < '2024-02-01';

Output:

-- Orders placed on a specific date
SELECT id, customer_id, total_amount
FROM orders
WHERE order_date = '2024-03-05';

Output:

Date Comparisons Follow Calendar Logic

Date comparisons work exactly as you would expect from a calendar. "Greater than" means "later in time," and "less than" means "earlier in time."

-- All customers who signed up in 2024 or later
SELECT first_name, last_name, signup_date
FROM customers
WHERE signup_date >= '2024-01-01';

Output:

Understanding NULL Values

NULL is one of the most important and most misunderstood concepts in SQL. It represents the absence of a value, meaning the data is unknown, missing, or not applicable.

NULL is not the same as:

• 0 (zero is a known numeric value)
• '' (an empty string is a known string value that happens to be empty)
• 'NULL' (this is a literal string containing the word "NULL")
• false (false is a known boolean value)

NULL means "we do not know." A customer's phone number might be NULL because they never provided one. An order's ship date might be NULL because it has not shipped yet.

Adding NULL Data to Our Example

To demonstrate NULL behavior, let us add a customer with some missing data and modify a product:

-- Add a customer with no city
INSERT INTO customers VALUES (7, 'Grace', 'Taylor', 'grace@email.com', NULL, '2024-04-01');

-- Check the result
SELECT first_name, last_name, city
FROM customers
WHERE id = 7;

Output:

Grace's city is NULL, meaning we do not have that information.

The NULL Trap: Why = and != Do Not Work

Here is where most beginners get caught. Your natural instinct is to find NULL values like this:

-- This does NOT work as expected
SELECT first_name, last_name, city
FROM customers
WHERE city = NULL;

Output:

(empty result set, zero rows)

Even though Grace's city is NULL, this query returns nothing. Why?

Because NULL in SQL follows three-valued logic. Any comparison with NULL using standard operators does not return true or false. It returns UNKNOWN. And the WHERE clause only includes rows that evaluate to true, not UNKNOWN.

The logic works like this:

Since UNKNOWN is not true, the row is excluded. This applies to all comparison operators:

-- None of these will find NULL rows
WHERE city = NULL      -- UNKNOWN, row excluded
WHERE city != NULL     -- UNKNOWN, row excluded
WHERE city <> NULL     -- UNKNOWN, row excluded
WHERE city > NULL      -- UNKNOWN, row excluded

IS NULL: Finding Missing Values

To properly test for NULL, use the IS NULL operator:

SELECT first_name, last_name, city
FROM customers
WHERE city IS NULL;

Output:

Now the query correctly finds Grace, whose city is missing.

IS NOT NULL: Finding Non-Missing Values

To find rows where a value exists (is not NULL), use IS NOT NULL:

SELECT first_name, last_name, city
FROM customers
WHERE city IS NOT NULL;

Output:

All six customers with known cities are returned. Grace, whose city is NULL, is excluded.

Real-World NULL Scenarios

NULL values appear constantly in real databases. Here are common scenarios where you would filter by NULL:

-- Find products that have no category assigned
SELECT name
FROM products
WHERE category_id IS NULL;

-- Find orders that have not been given a total yet
SELECT id, order_date
FROM orders
WHERE total_amount IS NULL;

-- Find customers who HAVE provided their city
SELECT first_name, last_name, city
FROM customers
WHERE city IS NOT NULL;

The != NULL Mistake in Practice

This mistake is so common and so subtle that it deserves extra emphasis. Let us look at a realistic scenario where it causes real problems.

Imagine you want to find all customers who are not from Chicago:

-- Attempt: Find customers not from Chicago
SELECT first_name, last_name, city
FROM customers
WHERE city != 'Chicago';

Output:

This looks correct at first glance, but Grace is missing. She is not from Chicago (her city is NULL, meaning unknown), yet she was excluded because NULL != 'Chicago' evaluates to UNKNOWN, not true.

If you want to include customers with unknown cities, you need to handle NULL explicitly:

-- Correct: Include customers with unknown cities
SELECT first_name, last_name, city
FROM customers
WHERE city != 'Chicago' OR city IS NULL;

Output:

Now Grace is properly included in the results.

Combining Everything: Mixed Filtering Examples

Let us put all the concepts together with queries that filter different data types.

Finding Available Products Under $50

SELECT name, price, is_available
FROM products
WHERE price < 50
  AND is_available = true;

Output:

Finding Orders from a Specific Date Range

SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date >= '2024-03-01'
  AND order_date <= '2024-03-31';

Output:

This finds all orders placed during March 2024.

Finding Customers from a Specific City Who Signed Up Recently

SELECT first_name, last_name, city, signup_date
FROM customers
WHERE city = 'Chicago'
  AND signup_date >= '2024-01-01';

Output:

Only Frank matches both conditions: lives in Chicago and signed up in 2024 or later.

Practical Exercises

Test your understanding with these exercises. Write the query first, then check your answer against the expected output.

Exercise 1

Find all products that cost exactly $45.00.

SELECT name, price
FROM products
WHERE price = 45.00;

Expected output:

Exercise 2

Find all orders with a status that is not "pending."

SELECT id, order_date, status
FROM orders
WHERE status != 'pending';

Expected output:

Exercise 3

Find all products with stock quantities of zero.

SELECT name, stock_quantity, is_available
FROM products
WHERE stock_quantity = 0;

Expected output:

Exercise 4

Find all customers whose city is unknown (NULL).

SELECT first_name, last_name, city
FROM customers
WHERE city IS NULL;

Expected output:

Exercise 5

Find all customers who have a known city (not NULL) and signed up before 2024.

SELECT first_name, last_name, city, signup_date
FROM customers
WHERE city IS NOT NULL
  AND signup_date < '2024-01-01';

Expected output:

Key Takeaways

The WHERE clause transforms SQL from a blunt instrument into a precision tool. Here is what you should remember:

• WHERE filters rows based on a condition, placed after FROM
• Six comparison operators let you test for equality, inequality, and ranges: =, !=/<>, <, >, <=, >=
• Text values must be wrapped in single quotes: WHERE city = 'New York'
• Numeric values are written without quotes: WHERE price > 50
• Date values use the 'YYYY-MM-DD' format in single quotes: WHERE order_date > '2024-01-01'
• NULL represents missing or unknown data, and it cannot be found with = or !=
• Use IS NULL to find missing values and IS NOT NULL to find existing values
• Always consider whether a column might contain NULL when writing conditions, especially with !=

With SELECT, FROM, and WHERE in your toolkit, you can now write targeted queries that answer specific questions about your data. The next step is learning how to combine multiple conditions using AND, OR, and NOT to build even more powerful filters.