SQL online course

SQL WHERE Clause: Filter Rows with Conditions

Learn how to use the SQL WHERE clause to filter table rows with equality, comparison operators, BETWEEN, and IS NULL.

The SQL WHERE clause filters rows according to a condition. It lets you return only the records that meet specified search criteria.

This lesson assumes you understand database tables, rows, columns, basic data types, and the SQL SELECT statement.

What the WHERE Clause Does

A WHERE clause specifies which rows qualify for inclusion in a query result. This process is called filtering: restricting returned rows according to one or more criteria.

WHERE filters rows, not columns. The SELECT clause determines which columns appear in the result, while the WHERE clause determines which records qualify.

  • SELECT chooses columns such as id, name, and city.
  • WHERE chooses rows whose values satisfy a condition.

A query without a WHERE clause can return every row in the source table. A query with a WHERE clause returns only rows that match its condition.

-- May return every row and every column
SELECT * FROM customer;

-- Returns only customers whose city is Hope
SELECT * FROM customer
WHERE city = 'Hope';

Basic SELECT ... FROM ... WHERE Syntax

SELECT column_name1, column_name2
FROM table_name
WHERE condition;

The parts of this statement have different jobs:

  • Column names after SELECT identify the columns to display.
  • Table name after FROM identifies the table to read.
  • WHERE begins the filtering clause.
  • A comparison operator, such as = or >, compares values.
  • The comparison value is the value or expression used in the test.

Use SELECT * when you need every column from the qualifying rows.

SELECT *
FROM customer
WHERE city = 'Hope';

A condition is a logical test. For each row, the database evaluates the condition and includes the row when the test is true. A condition that evaluates to false does not include the row.

Sample Customer Table

The examples use this customer table:

Columns: id, name, address, city, state, zip

Row 1: 1 | Bill Smith | 123 Main Street | Hope | CA | 98765

Row 2: 2 | Mary Smith | 123 Dorian Street | Harmony | AZ | 98765

Row 3: 3 | Bob Smith | 123 Laugh Street | Hope | CA | 98765

Row 4: 4 | Chang Chao | 123 Dorian Streat | Hong Kong | CN | 98765

Filter Customers with Equality

The equality operator, =, tests whether two values are equal. To find customers whose city is Hope, compare the city column with the text literal 'Hope'.

SELECT *
FROM customer
WHERE city = 'Hope';

Only rows whose city value equals Hope qualify. The query returns Bill Smith and Bob Smith:

id | name | address | city | state | zip

1 | Bill Smith | 123 Main Street | Hope | CA | 98765

3 | Bob Smith | 123 Laugh Street | Hope | CA | 98765

The selected columns and the filtering condition are independent. You can display only a few columns while still filtering by any available column.

SELECT id, name, city
FROM customer
WHERE state = 'CA';

This returns the id, name, and city columns for customers whose state is California.

WHERE Comparison Operators

A comparison operator compares a column value with another value or expression. Common operators include equality, non-equality, and ordered comparisons.

Operator | Meaning | Example condition

= | Equal to | city = 'Hope'

<> | Not equal to | state <> 'CA'

!= | Not equal to in many SQL dialects | state != 'CA'

< | Less than | id < 3

<= | Less than or equal to | id <= 3

> | Greater than | id > 1

>= | Greater than or equal to | id >= 1

!< | Not less than where supported | id !< 3

!> | Not greater than where supported | id !> 3

BETWEEN | Within a specified range | id BETWEEN 1 AND 3

IS NULL | Has a NULL value | address IS NULL

Non-equality Filtering

The standard SQL non-equality operator is <>. It returns rows where the values differ.

SELECT *
FROM customer
WHERE city <> 'Hope';

The != operator is also supported by many database systems, but support can vary. Prefer <> when you want the standard SQL form.

Numeric and Ordered Comparisons

Use <, <=, >, and >= with numeric values and other ordered values.

SELECT id, name
FROM customer
WHERE id > 1;

This returns customers whose identifier is greater than 1. Numeric values are written without quotes in this example. Ordered comparisons may also be used with dates or other values supported by the database system.

The forms !< and !> mean “not less than” and “not greater than” in database products that support them. They are not universally supported, so check your database documentation before using them.

Range Filtering with BETWEEN

BETWEEN tests whether a value falls within a range. The endpoints are normally inclusive, so the following condition includes IDs 1, 2, and 3.

SELECT id, name
FROM customer
WHERE id BETWEEN 1 AND 3;

For a different data type, such as dates, confirm the database system's rules for literals and range boundaries.

Checking for NULL

NULL marks an absent or unknown value. It is not the same as an empty string, zero, or a word such as “unknown.” Because NULL represents an unknown value, do not test it with the equality operator.

-- Correct
SELECT id, name
FROM customer
WHERE address IS NULL;

Do not write address = NULL when looking for missing addresses. Use IS NULL. To test for a value that is not NULL, use IS NOT NULL.

Writing WHERE Conditions Correctly

Quote Text Literals

Write text literals in single quotes. For example:

WHERE city = 'Hope'

Without quotes, a database may interpret Hope as a column name or another identifier, causing a syntax error or an unintended result. Numeric literals such as 3 generally do not need quotes.

Put the Column on the Left in Simple Conditions

For readable beginner-level conditions, place the column name on the left and the test value or expression on the right.

WHERE state = 'CA'
WHERE id >= 2
WHERE city <> 'Hope'

More advanced SQL can compare expressions, but simple column-to-value conditions make the purpose of a filter clear.

Remember Database-Specific Differences

SQL is implemented by several database systems. Most support the common operators described here, but syntax and behavior can differ for operators such as !=, !<, and !>, as well as text comparison and case sensitivity.

When portability matters, use standard forms such as <> for non-equality and verify the behavior of your specific database.

Common Problems and Corrections

  • Text comparison causes an error or misses a match: quote the text value with single quotes, as in WHERE city = 'Hope'.
  • The query returns every row: the WHERE clause may be missing, or its condition may be too broad. Add a condition that identifies the intended records.
  • A search for missing values returns no rows: replace = NULL with IS NULL.
  • A non-equality operator fails: use the standard <> operator and check whether your database supports alternatives such as !=, !<, or !>.

Exam-Relevant Notes

  • SELECT chooses columns; WHERE filters rows.
  • A query without WHERE may return all rows from its source table.
  • Use = for equality, not ==.
  • Use single quotes around text literals such as 'Hope'.
  • Use <> for standard SQL non-equality.
  • Use BETWEEN for range tests; its endpoints are normally included.
  • Use IS NULL to test for missing values, not = NULL.

Next Steps

After learning single conditions, combine conditions with SQL AND and OR operators. You can also explore the SQL BETWEEN operator, SQL IN operator, and SQL LIKE operator for more flexible filtering. Use SQL ORDER BY when you also need to control result order.