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:

name	price
Mechanical Keyboard	89.99
Coffee Maker Pro	129.99
Running Shoes X1	110.00
Bluetooth Speaker	65.00

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.

Mental Model

Imagine the database going through each row one by one and asking: "Does this row's price exceed 50?" If yes, the row is included. If no, the row is skipped. This happens for every row in the table before the results are returned.

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.

Operator	Meaning	Example
=	Equal to	price = 29.99
!= or <>	Not equal to	status != 'pending'
<	Less than	price < 50
>	Greater than	price > 100
<=	Less than or equal to	stock_quantity <= 30
>=	Greater than or equal to	price >= 89.99

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:

first_name	last_name	city
Carol	Singh	Chicago
Frank	Wilson	Chicago

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

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

Output:

id	customer_id	total_amount	status
1	1	119.98	completed
2	2	89.99	completed
3	1	77.49	completed

Common Mistake: Using == Instead of =

If you come from programming languages like Python, JavaScript, or C, you might instinctively write == for equality comparisons. In SQL, equality is always a single equals sign.

-- Wrong: Double equals is not valid SQL
SELECT name FROM products WHERE price == 29.99;
-- ERROR: syntax error

-- Correct: Single equals sign
SELECT name FROM products WHERE price = 29.99;

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:

name	price	is_available
Bluetooth Speaker	65.00	false

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:

id	status
1	completed
2	completed
3	completed
4	shipped
5	shipped

Which Should You Use: != or <>?

Both work in all major databases. <> is the ANSI SQL standard and is guaranteed to work everywhere. != is more familiar to developers coming from other programming languages. Choose whichever your team prefers and use it consistently.

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:

name	price
Wireless Mouse	29.99
SQL for Beginners	34.99
Stainless Water Bottle	24.99

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

Output:

name	price
Coffee Maker Pro	129.99
Running Shoes X1	110.00

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:

name	price
Wireless Mouse	29.99
USB-C Hub	45.00
SQL for Beginners	34.99
Data Science Handbook	42.50
Yoga Mat Premium	38.00
Stainless Water Bottle	24.99

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:

name	stock_quantity
Wireless Mouse	150
USB-C Hub	200
Yoga Mat Premium	100
Stainless Water Bottle	180

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:

first_name	last_name	email
Bob	Martinez	bob@email.com

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";

Double Quotes Mean Something Different in SQL

In most SQL databases, double quotes are reserved for identifiers (table names, column names), not for data values.

-- Single quotes: This is a data value (correct for filtering)
WHERE city = 'New York'

-- Double quotes: This refers to a column or table name
WHERE "city" = 'New York'

In MySQL, double quotes around data values may work if the ANSI_QUOTES mode is disabled (which is the default), but this is non-standard behavior. In PostgreSQL and SQL Server, using double quotes around a data value will cause an error or be interpreted as a column reference.

Always use single quotes for string values. It is correct in every database.

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):

first_name	last_name	city
Alice	Johnson	New York
David	Chen	New York

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:

first_name	last_name
Eva	Brown
David	Chen

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:

name
Wireless Mouse
Mechanical Keyboard
USB-C Hub
SQL for Beginners
Yoga Mat Premium
Running Shoes X1
Stainless Water Bottle

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:

name	price
Mechanical Keyboard	89.99

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

Output:

id	customer_id	total_amount
1	1	119.98
4	3	129.99
5	4	155.00
8	6	199.98

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

Output:

name	stock_quantity
Data Science Handbook	30
Coffee Maker Pro	25
Bluetooth Speaker	0

Common Mistake: Quoting Numbers

Do not wrap numeric values in quotes. While some databases tolerate it, it can cause unexpected behavior or performance problems.

-- Wrong: Treating a number as a string
SELECT name FROM products WHERE price = '89.99';
-- May work in some databases but forces a type conversion

-- Correct: Numbers should not have quotes
SELECT name FROM products WHERE price = 89.99;

The database may silently convert the string '89.99' to a number, but this implicit conversion can cause performance issues because the database cannot use indexes efficiently when types do not match.

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:

first_name	last_name	signup_date
Carol	Singh	2023-06-10
David	Chen	2023-08-05
Eva	Brown	2024-01-18
Frank	Wilson	2024-02-28

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

Output:

id	order_date	total_amount
1	2024-01-10	119.98
2	2024-01-15	89.99

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

Output:

id	customer_id	total_amount
4	3	129.99

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:

first_name	last_name	signup_date
Eva	Brown	2024-01-18
Frank	Wilson	2024-02-28

Date Format Matters

The 'YYYY-MM-DD' format (ISO 8601) is the safest and most universally accepted date format across all SQL databases. Other formats like 'MM/DD/YYYY' or 'DD-MM-YYYY' may work in some databases but can cause ambiguity and errors in others. Always use 'YYYY-MM-DD'.

-- Safe: Works in every database
WHERE order_date > '2024-03-15'

-- Risky: Different databases interpret this differently
WHERE order_date > '03/15/2024'

-- Risky: Is this March 15 or the 3rd of the 15th month?
WHERE order_date > '15-03-2024'

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:

first_name	last_name	city
Grace	Taylor	NULL

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:

Expression	Result
NULL = NULL	UNKNOWN (not true!)
NULL != NULL	UNKNOWN
NULL > 5	UNKNOWN
NULL = 'Chicago'	UNKNOWN
NULL != 'Chicago'	UNKNOWN

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

This Is Not a Bug

The behavior of NULL with comparison operators is not a bug or an oversight. It is how SQL was intentionally designed, based on formal logic. If you do not know someone's city (NULL), you cannot say their city equals Chicago, and you also cannot say it does not equal Chicago. You simply do not know. This is why the result is UNKNOWN rather than true or false.

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:

first_name	last_name	city
Grace	Taylor	NULL

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:

first_name	last_name	city
Alice	Johnson	New York
Bob	Martinez	Los Angeles
Carol	Singh	Chicago
David	Chen	New York
Eva	Brown	Seattle
Frank	Wilson	Chicago

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:

first_name	last_name	city
Alice	Johnson	New York
Bob	Martinez	Los Angeles
David	Chen	New York
Eva	Brown	Seattle

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:

first_name	last_name	city
Alice	Johnson	New York
Bob	Martinez	Los Angeles
David	Chen	New York
Eva	Brown	Seattle
Grace	Taylor	NULL

Now Grace is properly included in the results.

Golden Rule for NULL

Whenever you write a WHERE condition, ask yourself: "Could this column contain NULL values? If so, does my condition handle them correctly?" This single habit will prevent countless subtle bugs in your queries.

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:

name	price	is_available
Wireless Mouse	29.99	true
USB-C Hub	45.00	true
SQL for Beginners	34.99	true
Data Science Handbook	42.50	true
Yoga Mat Premium	38.00	true
Stainless Water Bottle	24.99	true

Preview: AND and OR

This example uses the AND keyword to combine two conditions. You will learn about combining conditions with AND, OR, and NOT in the next guide. For now, just note that AND means both conditions must be true for a row to be included.

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:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending

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:

first_name	last_name	city	signup_date
Frank	Wilson	Chicago	2024-02-28

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:

name	price
USB-C Hub	45.00

Exercise 2

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

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

Expected output:

id	order_date	status
1	2024-01-10	completed
2	2024-01-15	completed
3	2024-02-20	completed
4	2024-03-05	shipped
5	2024-03-12	shipped

Exercise 3

Find all products with stock quantities of zero.

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

Expected output:

name	stock_quantity	is_available
Bluetooth Speaker	0	false

Exercise 4

Find all customers whose city is unknown (NULL).

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

Expected output:

first_name	last_name	city
Grace	Taylor	NULL

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:

first_name	last_name	city	signup_date
Alice	Johnson	New York	2023-01-15
Bob	Martinez	Los Angeles	2023-03-22
Carol	Singh	Chicago	2023-06-10
David	Chen	New York	2023-08-05

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.