Filter with WHERE
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 6 of 20.
WHERE keeps rows for which a condition is true. A comparison uses a column, an operator and a value. Numbers are unquoted; a text value uses single quotes.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, name FROM products WHERE price > 20 ORDER BY id;Only Atlas and lamp cost more than 20. A price of exactly 20 would be excluded because > means strictly greater. The filter can use a column that is not in the output.
Use WHERE to keep matching rows. = means equal and >= includes the boundary.
Challenge
EasyReturn id and name for products whose stock is zero. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
2Filter the Catalog
Filter with WHERECombine ConditionsMissing Values with NULLSearch Text with ILIKEFind Dispatch CandidatesPractice on your own: PostgreSQL playground