Add a WHERE clause to a SELECT query in SQL to return only rows that satisfy a condition. The SELECT list chooses which columns to show; WHERE chooses which rows qualify. Start with a small table and predict the result before running the query.
Suppose a library keeps a books table. The copies column is the number available, and NULL means the count has not been recorded.
| id | title | category | copies |
|---|---|---|---|
| 1 | Cedar | History | 3 |
| 2 | Harbor | History | 0 |
| 3 | River | Science | 2 |
| 4 | Summit | Science | NULL |
| 5 | Lantern | Art | 1 |
| 6 | Meadow | Art | 4 |
SELECT WHERE SQL Examples: Filter a Table
Find books with more than one available copy:
SELECT id, title, copies
FROM books
WHERE copies > 1
ORDER BY id;
The result contains (1, Cedar, 3), (3, River, 2), and (6, Meadow, 4). Rows 2 and 5 do not meet the comparison. Row 4 has an unknown count and is excluded too. ORDER BY id makes the order in this example explicit; without it, SQL does not promise a particular row order.
An SQL SELECT WHERE condition is tested against each input row. Only a TRUE result passes the filter. The column in the condition need not appear in the SELECT list: you could return just title while still filtering on copies. Conversely, listing a column after SELECT does not filter it.
Compare numbers and text
Change the condition to find books in one category:
SELECT id, title
FROM books
WHERE category = 'History'
ORDER BY id;
The result is IDs 1 and 2, with titles Cedar and Harbor. Text literals use single quotes; History is a value, while category names a column. A SELECT with WHERE in SQL can use comparisons such as =, <>, >, >=, <, and <=. For example, copies >= 2 returns IDs 1, 3, and 6 on this table.
A WHERE clause in a SQL SELECT can be checked against a boundary row before you run it. Boundary values matter. copies > 2 excludes River because its count is exactly 2; copies >= 2 includes it. When building an SQL SELECT WHERE filter for a threshold, write down whether equality should pass. Text comparison behavior, including case sensitivity, can depend on the database and collation, so test the rule on the system holding your data.
A SELECT WHERE SQL example also reveals a common confusion: WHERE category = 'History' restricts rows even if category is absent from the output. Add that column temporarily if you want to inspect why each result matched, then remove it when you only need titles.
Combine tests for one row
To find available Science books, require both the category and the count condition:
SELECT id, title, copies
FROM books
WHERE category = 'Science' AND copies > 0
ORDER BY id;
Only ID 3, River, is returned. ID 4 is in Science, but its count is unknown, so the full condition is not TRUE. In an SQL query, SELECT with WHERE can combine conditions with AND when each requirement must hold.
To accept alternative categories, use OR and make the alternatives visible:
SELECT id, title
FROM books
WHERE category = 'History' OR category = 'Art'
ORDER BY id;
The result is IDs 1, 2, 5, and 6. This SQL query selects with WHERE by category, so both alternatives are evaluated for each book. The SQL SELECT WHERE clause here does not test availability, so Harbor is included even though its count is zero. If availability matters, add the requirement and group the alternatives:
SELECT id, title
FROM books
WHERE (category = 'History' OR category = 'Art')
AND copies > 0
ORDER BY id;
Now IDs 1, 5, and 6 remain. The parentheses show that the availability test applies to both categories. This SELECT WHERE SQL pattern makes the intended set of alternatives clear before another requirement is added.
Find missing values with IS NULL
A missing count is different from a count of zero. To find the unrecorded value, use:
SELECT id, title
FROM books
WHERE copies IS NULL;
The result is ID 4, Summit. In SQL, selecting with WHERE and IS NULL isolates the records that need a count. copies = NULL does not match it: a comparison with NULL produces UNKNOWN, and WHERE keeps only TRUE. Use IS NOT NULL for rows whose count is recorded; here that returns IDs 1, 2, 3, 5, and 6. In a SQL SELECT with WHERE, decide separately whether missing entries should be included before interpreting the result.
Filter rows before groups, then filter groups
Suppose you want categories containing at least two books with a recorded positive count. WHERE filters the individual books before grouping; HAVING filters the resulting groups:
SELECT category, COUNT(*) AS available_titles
FROM books
WHERE copies > 0
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;
WHERE keeps IDs 1, 3, 5, and 6. Grouping produces History with one title, Science with one, and Art with two. This SELECT query in SQL uses WHERE to define the records counted before any group is tested. HAVING returns only (Art, 2). The SQL SELECT WHERE condition defines which rows contribute to each count; HAVING applies the threshold to the count itself.
Remove WHERE and Art, History, and Science each have two rows, so all three groups pass HAVING. That difference is a useful check: the first query asks about available titles, while the second counts every row, including zero and missing counts. When you select rows with WHERE in SQL, make sure the row filter matches the population you intend to summarize.
Investigate an unexpected result
Suppose a report should show all books with at least one copy, but Summit is missing. Run a SQL SELECT using WHERE for that title and include the count in the output:
SELECT id, title, copies
FROM books
WHERE title = 'Summit';
The result is ID 4 with a NULL count. The original filter copies > 0 cannot decide that an unknown count is positive, so it excludes the row. The right fix depends on the question. If the report is a list of known available books, leaving the row out is consistent. If it is a list of books that might be available, use (copies > 0 OR copies IS NULL) and label the unknown count clearly.
On this table, that second condition returns IDs 1, 3, 4, 5, and 6; ID 2 remains out because zero is known and is not positive. In SQL, a SELECT with WHERE can therefore answer either question, but the condition must state which one you mean. When selecting with a WHERE clause in SQL, inspect the source values before changing a filter simply to obtain an expected total.
If more rows appear than expected, display the columns used in the predicate along with id. Check spelling, types, and whether each alternative should bypass any other requirement. A SQL SELECT using a WHERE condition can then be tested on each displayed row. Once the IDs are right, narrow the SELECT list to the columns needed in the final report.
Check the result before using it
For each SELECT query with a WHERE clause in SQL, identify the input table, the returned columns, and the row condition. Test one matching row, one boundary row, and one NULL. If the query groups results, verify which rows enter each group and apply the HAVING test to the resulting count. Finally, compare the expected IDs with the actual output; use ORDER BY when their order matters.
