Filtering and Sorting

In this lesson, you'll learn how to narrow down your query results with WHERE clauses and organize them with ORDER BY. These are essential skills for finding and presenting data effectively.

What You'll Learn

By the end of this lesson you will write a report query that keeps exactly the rows a question asks about and returns them in a defined order — and, just as importantly, you will be able to explain why a filter that looks obviously right can match nothing at all. Filters fail silently in SQL: an empty result is a legitimate answer, not an error, so nothing warns you when the condition you wrote is not the condition you meant.

This is the first-reports milestone of the warehouse project. Every later stage of that project is gated by a verification query — a small filtered, sorted SELECT you run to confirm the stage did what you think it did — and writing one that you can trust is the skill here. You arrive able to choose columns, compute values and cap results (Selecting Data); what is new is choosing rows, and doing it in a way that holds up when the data contains nulls.

The WHERE Clause

The WHERE clause filters rows based on conditions. Only rows that meet the condition are included in the results.

Basic Filtering

-- Create a customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    city TEXT,
    country TEXT
);

-- Insert sample data
INSERT INTO customers (id, name, age, city, country)
VALUES
    (1, 'Alice Johnson', 28, 'New York', 'USA'),
    (2, 'Bob Smith', 35, 'London', 'UK'),
    (3, 'Charlie Davis', 22, 'New York', 'USA'),
    (4, 'Diana Wilson', 31, 'Paris', 'France'),
    (5, 'Eve Brown', 26, 'London', 'UK');

-- Find customers from New York
SELECT * FROM customers WHERE city = 'New York' ORDER BY id;

-- Find customers older than 30
SELECT * FROM customers WHERE age > 30 ORDER BY id;

A WHERE clause is evaluated once per row, and the row is kept only when the condition comes out true. That is a narrower rule than "not false", and the difference is where most filtering bugs live — you will meet it in the NULL section below.

Matching one of several values with IN

When you want rows matching any of a short list of values, IN says it directly instead of chaining OR:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    city TEXT,
    country TEXT
);

INSERT INTO customers (id, name, age, city, country)
VALUES
    (1, 'Alice Johnson', 28, 'New York', 'USA'),
    (2, 'Bob Smith', 35, 'London', 'UK'),
    (3, 'Charlie Davis', 22, 'New York', 'USA'),
    (4, 'Diana Wilson', 31, 'Paris', 'France'),
    (5, 'Eve Brown', 26, 'London', 'UK');

-- These two queries ask exactly the same question
SELECT id, name, city FROM customers WHERE city = 'New York' OR city = 'London' ORDER BY id;
SELECT id, name, city FROM customers WHERE city IN ('New York', 'London') ORDER BY id;

IN is shorthand for a chain of equality tests joined by OR, so everything true of = is true of it — including that the comparison is against the values exactly as written, capital letters and all. There is one place the shorthand stops behaving like plain OR, and it involves nulls inside the list; the subqueries lesson returns to it once lists can be generated by a query rather than typed out.

Modify it twice, and predict before each Run. First, add 'paris' in lowercase to the IN list and predict whether Diana appears — she does not, because a stored 'Paris' and a written 'paris' are different strings. Then change the list to ('New York') alone and predict the row count: two, since New York is where Alice and Charlie both live.

Comparison Operators

SQL provides various operators for comparisons:

-- Create an inventory table
CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price DECIMAL(10, 2),
    stock INTEGER
);

-- Insert products
INSERT INTO inventory (id, product, price, stock)
VALUES
    (1, 'Laptop', 999.99, 5),
    (2, 'Mouse', 24.99, 100),
    (3, 'Keyboard', 79.99, 50),
    (4, 'Monitor', 299.99, 12),
    (5, 'Headphones', 149.99, 0);

-- Equal to
SELECT * FROM inventory WHERE stock = 0;

-- Not equal to
SELECT * FROM inventory WHERE stock != 0;

-- Greater than
SELECT * FROM inventory WHERE price > 100;

-- Less than or equal to
SELECT * FROM inventory WHERE stock <= 10;

-- Between (inclusive range)
SELECT * FROM inventory WHERE price BETWEEN 50 AND 300;

Logical Operators

Combine multiple conditions with AND, OR, and NOT:

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary INTEGER,
    years_experience INTEGER
);

-- Insert employees
INSERT INTO employees (id, name, department, salary, years_experience)
VALUES
    (1, 'John Doe', 'Engineering', 75000, 5),
    (2, 'Jane Smith', 'Marketing', 65000, 3),
    (3, 'Mike Johnson', 'Engineering', 85000, 8),
    (4, 'Sarah Williams', 'Sales', 70000, 4),
    (5, 'Tom Brown', 'Engineering', 60000, 2);

-- AND: Both conditions must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;

-- OR: At least one condition must be true
SELECT * FROM employees
WHERE department = 'Sales' OR years_experience > 5;

-- NOT: Negates a condition
SELECT * FROM employees
WHERE NOT department = 'Engineering';

-- Complex combinations using parentheses
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary >= 70000;

Pattern Matching with LIKE

Search for patterns in text using wildcards:

-- Create a books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT,
    genre TEXT
);

-- Insert books
INSERT INTO books (id, title, author, genre)
VALUES
    (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'),
    (2, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction'),
    (3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'),
    (4, 'Pride and Prejudice', 'Jane Austen', 'Romance'),
    (5, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy');

-- % matches any sequence of characters
SELECT id, title FROM books WHERE title LIKE 'The%' ORDER BY id;

-- Find books with "and" anywhere in the title
SELECT id, title FROM books WHERE title LIKE '%and%' ORDER BY id;

-- _ matches exactly one character
SELECT id, author FROM books WHERE author LIKE 'J._. _alinger' ORDER BY id;

Two things about that last pattern are worth slowing down for. Each _ stands for exactly one character, so the pattern has to account for every character in the value — J.D. Salinger has a period after the D, and a pattern that forgets it matches nothing at all and tells you so by returning an empty result. And % and _ are the only wildcards: a period in a LIKE pattern is an ordinary period, not the "any character" it means in a regular expression.

LIKE is case-insensitive here, and = is not. Run SELECT id, title FROM books WHERE title LIKE 'the%' ORDER BY id; and all three "The" titles come back, even though the stored values start with a capital. That is a documented behaviour of LIKE for ASCII characters specifically: SQLite's own reference states that it "only understands upper/lower case for ASCII characters by default", so 'a' LIKE 'A' is true while 'æ' LIKE 'Æ' is false (SQLite expression reference). It is also not shared by =, which compares text exactly as written. Over the books above, WHERE genre = 'fiction' matches nothing while WHERE genre LIKE 'fiction' matches all three Fiction rows — same data, same spelling, two different answers, and neither query reports anything unusual.

NULL Values

Handle missing data with IS NULL and IS NOT NULL:

-- Create a tasks table
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    task TEXT NOT NULL,
    assigned_to TEXT,
    completed_date TEXT
);

-- Insert tasks (some without assignments or completion dates)
INSERT INTO tasks (id, task, assigned_to, completed_date)
VALUES
    (1, 'Write documentation', 'Alice', '2025-01-10'),
    (2, 'Fix bug #123', 'Bob', NULL),
    (3, 'Design new feature', NULL, NULL),
    (4, 'Review code', 'Charlie', '2025-01-12'),
    (5, 'Update database', NULL, NULL);

-- Find unassigned tasks
SELECT * FROM tasks WHERE assigned_to IS NULL ORDER BY id;

-- Find completed tasks
SELECT * FROM tasks WHERE completed_date IS NOT NULL ORDER BY id;

A null is not a value. It is the recorded absence of one, which is why SQL gives it its own operators — IS NULL and IS NOT NULL — rather than letting you reach for the ordinary comparisons. Before reading on, work out for yourself what happens if you do reach for an ordinary comparison anyway.

CREATE TABLE tasks (id INTEGER PRIMARY KEY, task TEXT NOT NULL, assigned_to TEXT);
INSERT INTO tasks VALUES (1,'Write docs','Alice'),(2,'Fix bug',NULL),(3,'Design feature',NULL),(4,'Review code','Charlie');

SELECT id, task FROM tasks WHERE assigned_to = NULL ORDER BY id;

Keeping a null out of a comparison you did not mean to make

The same rule catches filters that had nothing to do with nulls. A condition like WHERE stock > 0 quietly drops any row whose stock is unknown, because NULL > 0 is null rather than false. That is often what you want, and it is never what you expected — so when a row goes missing from a filtered report, checking whether the filtered column can be null is the first thing to do, not the last.

Building Filters From Values You Did Not Write

Everything above filters on values you typed into the query yourself. Real reports filter on values that came from somewhere else: a search box, a URL, a form, an uploaded file. The moment that happens, the way you get the value into the query matters more than the filter itself.

The wrong way is to paste the value into the query text — building the SQL by gluing strings together, so that a value like New York becomes part of the statement the database parses. Whoever supplies the value then controls the statement, not just the data in it: a value chosen to close the quote and continue the statement can add clauses you never wrote, and this class of bug, SQL injection, remains one of the most exploited in the world.

The right way is a parameterized query, also called a prepared statement. You write the SQL once with a placeholder where the value goes — in SQLite the placeholder is a ? — and hand the value to the database separately:

SELECT id, name, city FROM customers WHERE city = ? ORDER BY id;

The database compiles that statement knowing that the ? is one value in one place. Whatever arrives there is a value and can never become syntax, no matter what characters it contains — so the defense is structural rather than a matter of filtering out dangerous input.

You cannot run this one. The Run button here sends a script of literal SQL with no program around it, and binding a parameter is something the program does through its database driver. So that fence is no-run and stays a shape to recognise rather than a thing to execute. Two caveats travel with it and are worth learning now, because both cause real bugs:

• The binding has to happen in the database driver, not in your own code. Some libraries offer a helper that escapes a value and splices it into the string for you. That is still string-building, and its correctness depends on the helper agreeing with the database about every quoting rule. Pass the value to the driver and let the driver keep it separate.
• A placeholder can only stand for a value, never for an identifier. You cannot write ORDER BY ? or FROM ? and bind a column or table name — the database has to know what it is sorting before it can plan the query. When a sort column really does come from the user, map the incoming text against a fixed allow-list of column names you wrote yourself, and reject anything not on it.

Sorting with ORDER BY

Sort your results in ascending or descending order:

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10, 2),
    rating DECIMAL(3, 2)
);

-- Insert products
INSERT INTO products (id, name, category, price, rating)
VALUES
    (1, 'Laptop Pro', 'Electronics', 1299.99, 4.5),
    (2, 'Wireless Mouse', 'Electronics', 29.99, 4.2),
    (3, 'Office Chair', 'Furniture', 199.99, 4.8),
    (4, 'Desk Lamp', 'Furniture', 39.99, 4.0),
    (5, 'USB Cable', 'Electronics', 9.99, 3.9);

-- Sort by price (ascending - lowest first)
SELECT id, name, price FROM products ORDER BY price ASC, id ASC;

-- Sort by price (descending - highest first)
SELECT id, name, price FROM products ORDER BY price DESC, id ASC;

-- Sort by multiple columns
SELECT id, name, category, price FROM products ORDER BY category ASC, price DESC, id ASC;

-- Combine WHERE and ORDER BY
SELECT id, name, rating FROM products
WHERE category = 'Electronics'
ORDER BY rating DESC, id ASC;

Every sort above carries a second key, and that is not decoration. When two rows tie on the sort column the database is free to return them in either order, and it may make a different choice on a different run or after the table changes. A tiebreaker on a column whose values are unique — the id here — makes the result reproducible, which is what turns a query into something you can check against an expected answer.

The clauses go in one order, for a reason

CREATE TABLE inventory (id INTEGER PRIMARY KEY, product TEXT NOT NULL, price_cents INTEGER NOT NULL, stock INTEGER NOT NULL);
INSERT INTO inventory VALUES (1,'Laptop',99999,5),(2,'Mouse',2499,100),(3,'Desk',29999,12),(4,'Lamp',3999,0),(5,'Cable',999,60);

The stocked table above is already built; the five lines below are the whole query that reads it.

When a Filter Matches Nothing

An empty result is the hardest SQL outcome to debug, because it looks the same whether the data genuinely holds no matching rows or the condition could never have been true in the first place. The routine is to decide, before running, roughly how many rows should come back — and when nothing does, to ask what your condition evaluates to for the rows you expected rather than rereading it for typos.

CREATE TABLE tasks (id INTEGER PRIMARY KEY, task TEXT NOT NULL, assigned_to TEXT, due_date TEXT);
INSERT INTO tasks VALUES (1,'Write docs','Alice','2025-01-10'),(2,'Fix bug',NULL,'2025-01-11'),(3,'Design feature',NULL,'2025-01-09'),(4,'Review code','Charlie','2025-01-12');
SELECT id, task, due_date FROM tasks WHERE assigned_to = NULL ORDER BY due_date, id;

4 row(s) affected
id | task | due_date
--------------------
3 | Design feature | 2025-01-09
2 | Fix bug | 2025-01-11

Try It Yourself

Here is the build task. The starter creates an inventory table, fills it with two categories and some missing stock counts, and then runs a query that answers the wrong question. Work the three TODOs until it reports the electronics you can actually sell.

CREATE TABLE inventory (id INTEGER PRIMARY KEY, product TEXT NOT NULL, category TEXT NOT NULL, price_cents INTEGER NOT NULL, stock INTEGER);
INSERT INTO inventory VALUES (1,'Laptop','Electronics',99999,5),(2,'Mouse','Electronics',2499,100),(3,'Desk','Furniture',29999,12),(4,'Lamp','Furniture',3999,0),(5,'Cable','Electronics',999,NULL);
-- TODO 1: return exactly three columns: product, price_cents, stock.
-- TODO 2: keep only rows in the Electronics category whose stock is known
--         and greater than zero. The Cable has an unknown stock and must not
--         appear; the Mouse has 100 and must.
-- TODO 3: sort by price_cents, dearest first, breaking ties by product name.
SELECT * FROM inventory;

5 row(s) affected
product | price_cents | stock
-----------------------------
Laptop | 99999 | 5
Mouse | 2499 | 100

Once it passes, try two variations, predicting the result before each Run. First, drop AND stock IS NOT NULL and Run again — predict whether the Cable reappears, and note that the answer is no, because the comparison with an unknown value still is not true. Then change the category filter to category IN ('Electronics','Furniture') and predict the row count: three, because the Desk joins the list while the Lamp's zero stock and the Cable's unknown stock both still exclude them.

CREATE TABLE inv (id INTEGER PRIMARY KEY, product TEXT NOT NULL, stock INTEGER);
INSERT INTO inv VALUES (1,'A',5),(2,'B',10),(3,'C',NULL),(4,'D',20);

SELECT id FROM inv WHERE stock = 5 OR stock = 10 ORDER BY id;
SELECT id FROM inv WHERE stock IN (5, 10) ORDER BY id;

SELECT id FROM inv WHERE NOT (stock = 5 OR stock = 10) ORDER BY id;
SELECT id FROM inv WHERE stock NOT IN (5, 10) ORDER BY id;

Practice Exercises

Try these exercises using the tables above:

1. Find all employees in Engineering with less than 5 years of experience
2. Get products priced between $10 and $100, sorted by price
3. Find books where the author's name starts with 'J' and the genre is Fiction
4. List all tasks that are assigned but not completed, ordered by assigned person
5. Find customers from USA or UK who are under 30 years old

Pro Tip: Always put WHERE before ORDER BY in your queries. The order is: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

Key Takeaways

• WHERE filters rows based on conditions
• Comparison operators: =, !=, >, <, >=, <=, BETWEEN
• Logical operators: AND, OR, NOT
• LIKE with % and _ enables pattern matching
• IS NULL and IS NOT NULL handle missing data
• ORDER BY sorts results (ASC ascending, DESC descending)
• Combine WHERE and ORDER BY to filter and sort data

Next Steps

So far you have been reading data, but databases are not read-only. The next lesson covers INSERT, UPDATE, and DELETE — the statements that let you add new records, change existing values, and remove rows you no longer need. You will also learn safety habits like testing your WHERE clause with SELECT before running a destructive update.