Level 2: Filtering rows with WHERE

Lesson

A table often holds many more rows than the ones you care about. WHERE is used to filter: it goes after FROM and keeps only the rows for which the condition is true. The others disappear from the result, but of course they stay in the table.

A condition is a comparison. You compare a column with a value using = (equal), <> (different), < (less than), > (greater than), <= (less than or equal) and >= (greater than or equal). “At least 47000” is written >= 47000, “more than 47000” is written > 47000: the nuance changes the result as soon as a value is exactly 47000.

Text is written between single quotes: 'Drama'. Without quotes, SQL thinks Drama is a column name. A number is written without quotes: 2018. With =, text comparison is case-sensitive: 'drama' is not equal to 'Drama'.

You can also compare two columns of the same row: WHERE seats_sold < capacity keeps the flights that are not full. Or compare the result of a calculation: WHERE capacity - seats_sold > 40.

A key point for what follows: SQL does not run the query in the order it is written. It first takes the table (FROM), then filters the rows (WHERE), and only then builds the requested columns (SELECT). This order of execution explains many rules you will meet later. For example, an alias created in the SELECT does not exist yet when the WHERE runs: in standard SQL, you therefore write the calculation again in the WHERE.

Syntax

SELECT columns
FROM my_table
WHERE column >= value;

Worked example

SELECT title, genre
FROM movies
WHERE genre = 'Drama';

Only the movies whose genre is exactly 'Drama' are kept.

Example result

Key points

• WHERE always comes after FROM.
• Text between quotes, numbers without quotes.
• <> means “different from”.

Common pitfalls

• Writing WHERE genre = Drama without quotes: SQL then looks for a column named Drama.
• Mixing up > and >=: “at least 47000” is written >= 47000.

SQLite specifics

SQLite accepts a SELECT alias in the WHERE: WHERE empty_seats > 40 works. PostgreSQL, SQL Server and Oracle reject it. Build the portable habit: write the calculation again in the WHERE.

Another difference: with =, SQLite compares text case-sensitively (BINARY collation). MySQL, with its default collation, ignores case: 'drama' = 'Drama' is true there.

The level’s 5 exercises

1. Guided · Show the title of the movies whose genre is 'Comedy'. (Table: movies)
2. Practice · Show the title and year of the movies released after 2018 (2018 excluded). (Table: movies)
3. Practice · Show the name and salary of the employees who earn at least 47000. (Table: staff)
4. Practice · Show the name and department of the employees who don't work in the 'IT' department. (Table: staff)
5. Challenge · Show the airline, origin, destination and number of empty seats (empty_seats) of the flights with more than 40 empty seats. (Table: flights)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic