SQL Where
WHERE is the part of a query that keeps only the rows that match a test. Rows that fail the test stay in the table but leave the answer.
Match a text value
This test compares the city column with the text Austin. Two customers live in Austin, so two rows come back.
SELECT name, city FROM customers WHERE city = 'Austin';Match a number
The product_id column holds numbers. The test writes the number 5 with no quotes around it.
SELECT name, price FROM products WHERE product_id = 5;No row matches
A test can match nothing at all. No customer lives in Paris, so the answer holds the heading and no rows.
SELECT name FROM customers WHERE city = 'Paris';Filter another table
WHERE works on any table. This query keeps the orders placed by customer 1.
SELECT order_id, order_date FROM orders WHERE customer_id = 1;Example
SELECT name, country FROM customers WHERE country = 'USA';Key points
- WHERE: the keyword that filters rows by a test
- The test compares a column with a value.
- Text values wear single quotes in a test.
- Numbers stand bare in a test, with no quotes.
- A row enters the answer only when its test comes out true.
- Use WHERE to ask for some rows instead of every row.
In this example, the WHERE part tests the country column of each customer row. Two rows hold the value USA, so those two rows pass the test. The answer shows the name and country of both customers.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
Which test finds the customers who live in Austin?