Querying with SELECT

A database is only as useful as the questions you can ask it. In SQL, almost every question starts with SELECT. You describe what you want — which rows, which columns, in what order — and the database decides how to get it. That is what it means for SQL to be declarative.

The basic shape

SELECT name, city            -- which columns
FROM customers               -- which table
WHERE city = 'Pune'          -- which rows
ORDER BY name                -- in what order
LIMIT 10;                    -- how many

A few building blocks cover most everyday queries:

Comparisons and logic: =, <>, <, >=, AND, OR, NOT.
Ranges and lists: BETWEEN 2022 AND 2024, IN ('Pune', 'Kochi').
Patterns: name LIKE 'A%' finds names starting with A.
Missing values: phone IS NULL. NULL means unknown, so phone = NULL never matches anything.

NULL means unknown

A missing value in SQL is NULL, and it doesn’t behave like an ordinary value. Any comparison with NULL gives neither TRUE nor FALSE but UNKNOWN — and WHERE keeps only the rows where the condition is TRUE. Click through the conditions and watch Meera and Sara, whose city is NULL:

InteractiveNULL and three-valued logicPick a condition

Loading visual…

Aggregates skip NULLs too: here COUNT(*) is 6 but COUNT(city) is 4, and AVG ignores NULL values rather than treating them as zero.

Summarizing: aggregates and GROUP BY

Aggregate functions reduce many rows to one value: COUNT(*), SUM(amount), AVG(amount), MIN, MAX.

SELECT COUNT(*) AS customers, MIN(signup_year) AS first_year
FROM customers;

To summarize per category, add GROUP BY. Each group becomes one output row. To filter groups by an aggregate — “cities with at least two customers” — use HAVING.

The order a query really runs in

We write SELECT first, but the database evaluates the clauses in a different, logical order. Scroll through the query and watch the rows change at each stage:

Scroll storyThe logical order of a SELECT queryStep 1 of 7

Loading visual…

1
FROM
Start with all rows of the customers table.
2
WHERE
Discard rows that fail the condition. Ravi (2022) and Dev (2021) signed up too early.
3
GROUP BY
Collapse the remaining rows into one group per city: Bengaluru has three customers, Pune two and Kochi one.
4
HAVING
Filter whole groups using aggregates. Kochi has fewer than two customers, so its group is removed.
5
SELECT
Now the output columns are computed — city, and COUNT(*) renamed as customers.
6
ORDER BY
Sort the result rows, most customers first. ORDER BY can use the alias customers because SELECT has already run.
7
LIMIT
Keep only the first row: Bengaluru, with three customers.

This order explains several common surprises:

You can’t filter on an aggregate in WHERE — the groups don’t exist yet. Use HAVING.
A column alias defined in SELECT usually can’t be used in WHERE, but can be used in ORDER BY.
LIMIT without ORDER BY returns an arbitrary set of rows, which may change between runs.

The database is free to execute the query differently — using indexes, reordering steps — as long as the result is the same as this logical order. How it chooses is the subject of lecture 4.

Try it yourself

Here is a small orders table in a real SQLite database, running in your browser. Run the examples, then change them: move the HAVING condition into WHERE and read the error, or remove the ORDER BY from the top-3 query.

InteractiveQuery an orders tablePick an example · edit · Run

Loading visual…

SQLite loads only when you press Run (about 0.7 MB). Everything runs on your device.

So far every query used a single table. Real questions span several — orders and customers and products. Next: joins.

In this lecture you will

The basic shape

NULL means unknown

Summarizing: aggregates and GROUP BY

The order a query really runs in

Try it yourself

Key takeaways

Check your understanding