Filtering Rows with WHERE

So far, every SELECT you have seen returns every row in a table. Most of the time though, you only want specific rows: one particular user, only the products under a certain price, only the posts written by one author. That is exactly what the WHERE clause is for.

Here is the shape:

SELECT * FROM users WHERE id = 5;

This returns only the row (or rows) where the condition after WHERE is true. In this case, only the user whose id column equals 5. Every other row is simply left out of the result.

Comparison operators

WHERE conditions are built from comparison operators, which should feel familiar from ordinary math:

• = means equal to
• != means not equal to
• > means greater than
• < means less than
• >= means greater than or equal to
• <= means less than or equal to

For example, to find every product that costs more than 10:

SELECT name, price FROM products WHERE price > 10;

Text values need quotes

When you compare a text column, wrap the value in quotes. Numbers do not need quotes. For example, to find a blog post by a specific author:

SELECT title FROM posts WHERE author = 'ana';

Combining conditions with AND and OR

You can combine more than one condition. AND means both conditions must be true, OR means at least one of them must be true:

SELECT name, price FROM products WHERE price > 10 AND category = 'shoes';

This only returns shoes that cost more than 10. Swap AND for OR and you would instead get anything that is either a shoe or costs more than 10, which is a much wider result, so it is worth thinking carefully about which one you actually mean.