SELECT name, city FROM customers returns all 12 customers. Often you need only some of them: the customers from Canada, the pending orders, the products that cost 6.00.
Keep the rows that match
Add WHERE and a condition after the table name:
SELECT name, city
FROM customers
WHERE country = 'CA';= means "is equal to". The database tests the condition on each row. When the condition is true, it keeps the row. When the condition is false, it removes the row.
customers
| id | name | country | city |
|---|---|---|---|
| 1 | Ava Chen | CA | Toronto |
| 2 | Liam Patel | CA | Peterborough |
| 3 | Sofia Rossi | IT | Milan |
| 4 | Noah Kim | US | Seattle |
| 5 | Mia Johnson | US | Austin |
| 6 | Lucas Silva | BR | Sao Paulo |
| 7 | Emma Dubois | FR | Lyon |
| 8 | Omar Haddad | CA | Ottawa |
WHERE chooses the rows. The SELECT list chooses the columns. The query above returns 3 rows and 2 columns.
Write the value correctly
| Value | Write it | Example |
|---|---|---|
| text | in single quotes | country = 'CA' |
| number | without quotes | id = 7, price = 6.00 |
| boolean | true or false, without quotes | is_member = true |
Double quotes are not for text. They mark a name, such as a column name or a table name:
SELECT * FROM customers WHERE country = "CA";
ERROR: column "CA" does not existPostgreSQL reads "CA" as the name of a column. There is no such column.
Text must match exactly
Text comparison is case-sensitive. 'ca' and 'CA' are different values, so WHERE country = 'ca' keeps no rows. A query that returns no rows is not an error. The result is empty.