VMware ESXi and vSphere Cluster Management

SQL WHERE Clause: Filter Rows with Conditions

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

The SQL WHERE clause filters rows returned by a query. It evaluates a condition for each row and includes only the rows for which that condition is satisfied.

Without a filter, a query can return every row in a table:

SELECT *
FROM customer;

Adding WHERE limits the result set to matching rows:

SELECT *
FROM customer
WHERE city = 'Hope';

Here, WHERE controls which rows appear. The SELECT list controls which columns appear.

Basic SELECT ... FROM ... WHERE syntax

SELECT column_name1, column_name2
FROM table_name
WHERE condition;

• SELECT column_name1, column_name2 chooses the columns displayed in the result set.
• FROM table_name identifies the table containing the source rows.
• WHERE condition describes which rows qualify.

Use SELECT * when you want every column, but only for rows that satisfy the condition:

SELECT *
FROM customer
WHERE city = 'Hope';

The asterisk does not disable filtering. It means “return every column for the qualifying rows.”

Example: filter customers by city

Suppose the customer table contains these rows:

Run this query to return customers whose city is Hope:

SELECT *
FROM customer
WHERE city = 'Hope';

The filtered result set contains only the two matching records:

Rows 1 and 3 appear because their city value equals Hope. Rows 2 and 4 do not appear because their city values are different.

Equality filtering with =

The equality operator, =, tests whether two values are equal. Text values are written as string literals, normally enclosed in single quotation marks:

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

This query returns the selected columns for customers whose state is CA. The condition is checked row by row.

Data-type-aware conditions

A comparison value should be compatible with the data type of the column being filtered.

• Use quoted string literals for text columns: city = 'Hope'.
• Use numeric values for numeric columns: id > 2.
• Postal codes may be stored as text, especially when they can contain leading zeroes. Follow the column's actual data type.

SELECT id, name
FROM customer
WHERE id > 2;

This keeps rows whose numeric identifier is greater than 2. Writing 2 without quotes expresses a numeric comparison.

WHERE comparison and conditional operators

A comparison operator compares a column value with another value or expression. Common operators include:

For most portable SQL, prefer <> for “not equal” and ordinary comparison operators such as <= instead of dialect-specific forms like !>. Support for !=, !<, and !> can vary by database product.

Filtering a range with BETWEEN

BETWEEN tests whether a value falls within a lower and upper bound. In standard SQL, both endpoints are included:

SELECT id, name
FROM customer
WHERE id BETWEEN 2 AND 4;

This includes identifiers 2, 3, and 4. For a dialect with unusual behavior, confirm the treatment of range endpoints in its documentation.

Understanding NULL and IS NULL

NULL represents missing, unknown, or inapplicable data. It is not an ordinary text value, zero, or an empty string.

Test for a missing value with IS NULL:

SELECT id, name
FROM customer
WHERE address IS NULL;

To find rows where a value is present, use the complementary condition IS NOT NULL:

SELECT id, name
FROM customer
WHERE address IS NOT NULL;

Do not write address = NULL. Ordinary equality does not test for NULL, so that condition will not correctly find missing addresses.

Choosing columns and filtering rows separately

The selected columns and the filter condition have different jobs:

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

• SELECT id, name, city determines which columns are displayed.
• WHERE state = 'CA' determines which rows are included.

You can return a small set of columns while filtering against a different column.

Troubleshooting WHERE queries

No rows are returned unexpectedly

Check whether the stored value differs in spelling, spacing, or capitalization behavior. Also verify the table and column names, and confirm that the comparison value matches the actual data. Inspect sample or distinct values in the target column when necessary.

Text filtering causes a syntax error

Make sure the text is enclosed in matching single quotation marks:

WHERE city = 'Hope'

Unmatched quotation marks or unsupported quote syntax can cause an error.

= NULL does not find missing values

NULL requires a null test, not ordinary equality:

WHERE address IS NULL

An operator works in one database but not another

SQL database products can differ in their support for non-equality syntax, especially !=, !<, and !>. Prefer broadly supported operators and check the documentation for the database you are using.

Key points

• The WHERE clause restricts a result set to rows satisfying a condition.
• SELECT * returns every column, but only from rows that pass the filter.
• Use = for equality and single quotes around text values.
• Use numeric comparisons with numeric columns and compatible values.
• Use BETWEEN for a bounded range; standard SQL includes both endpoints.
• Use IS NULL or IS NOT NULL to test missing-value status.
• Check database-specific documentation before using less-portable operators.