WHERE: Keeping Only the Rows You Want

A reminder from lesson 1-1, where you built a pets table with the columns name, species, and age. The query that prints every column of every row is:

SELECT * FROM pets;

The * is shorthand for "all columns". There is no SELECT all and no bare SELECT pets in SQL, so the star is the one form to remember. This lesson keeps that shape and adds a way to cut the rows down.

Filtering rows

In lesson 1-2 you chose columns. Now you will choose rows, and this is the skill you will use most. A production table holds millions of rows, and almost every query a real app runs wants just a few of them: logging you in is WHERE email = ..., opening your cart is WHERE user_id = .... The WHERE clause keeps only the rows that pass a test:

SELECT title, year FROM movies WHERE year >= 2015;

The database checks the test against every row. Rows where it is true stay, the rest are dropped. The comparison operators are the ones from math:

This unit uses a movies table with columns title, year, rating, genre. Run the block below and check that only movies from 2015 or later come back.

Filtering movies by year

Five movies go into the table, and WHERE year >= 2015 keeps only three of them in the result.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, year FROM movies WHERE year >= 2015;

Output

Inside Out|2015
Arrival|2016
Paddington 2|2017

Inside Out makes the cut because >= includes the boundary value, and 2015 is exactly 2015. Writing > instead would have dropped it and returned only two rows, which is the single most common off-by-one mistake in a WHERE clause.

The clause order is fixed: SELECT columns, then FROM table, then WHERE condition. SELECT narrows the result sideways to fewer columns, and WHERE narrows it downwards to fewer rows.

Comparing text

WHERE works on text columns too. Use = with the value in single quotes:

SELECT title FROM movies WHERE genre = 'scifi';

Two details that trip beginners:

• SQL uses a single = for comparison (unlike == in Python or JavaScript).
• Text comparisons are exact about spelling: 'scifi' will not match 'Sci-Fi'. Keep your data consistent, or use LIKE (lesson 2-3) for looser matching.

Filtering on a decimal column

Swapping the condition to test the rating column returns the three movies rated above 8.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies WHERE rating > 8;

Output

Inside Out|8.1
The Matrix|8.7
Alien|8.5

Only the last line changed from the previous query, and the condition now tests rating rather than year. "Above 8" means strictly greater, so > is correct here and Arrival at 7.9 and Paddington 2 at 7.8 are both excluded.

The rating column is declared REAL, meaning a decimal number, and comparing it to the whole number 8 is fine. SQL compares numbers by value, so there is no need to write 8.0.

Filtering on a text column

Text comparisons work the same way as numeric ones, with two syntax details to get right: a single = for equality, and single quotes around the value.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title FROM movies WHERE genre = 'scifi';

Output

The Matrix
Arrival
Alien

The WHERE clause goes after FROM movies, and the test is genre = 'scifi' with the value in single quotes.

SQL uses one = for equality, not the == of Python, JavaScript, and Java. And a value in double quotes means something different in SQL, an identifier such as a column name, so genre = "scifi" is a genuine error rather than a style choice.