SELECT, WHERE, ORDER BY

Why it matters

Most SQL anyone writes is reading data: "which orders shipped late?", "what are our ten most expensive products?", "which users signed up this month?". All of those start with the same three clauses: SELECT chooses columns, WHERE chooses rows, and ORDER BY sorts them. Learn these well and you can already answer real questions.

The shape of a query

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

The clauses must appear in this order. Only SELECT and FROM are required. Examples below use this books table:

SELECT: choose columns

SELECT * returns every column. It is handy for a first look, but in real queries list the columns you need. It is clearer, and it keeps working if someone adds a column later.

SELECT title, author FROM books;

You can rename a column in the output with AS, which is called an alias:

SELECT title AS book, price AS price_usd FROM books;

SELECT DISTINCT removes duplicate rows from the result. SELECT DISTINCT author FROM books; lists Andy Weir once, not twice.

WHERE: choose rows

WHERE keeps only rows where the condition is true.

SELECT title, price
FROM books
WHERE price < 15;

Comparison operators: =, <> (not equal; != also works in most engines), <, <=, >, >=. Text values go in single quotes: WHERE genre = 'Memoir'. Double quotes are for column names in standard SQL, so "Memoir" can surprise you.

Combine conditions with AND, OR and NOT:

SELECT title
FROM books
WHERE genre = 'Science fiction' AND published_year >= 2000;

When you mix AND and OR, use parentheses. AND binds tighter, so without them the query may not mean what you think:

-- Memoirs or sci-fi, but only the cheap ones
WHERE (genre = 'Memoir' OR genre = 'Science fiction') AND price < 15

Three shortcuts make conditions easier to read:

• IN matches any value in a list: WHERE author IN ('Andy Weir', 'Tara Westover').
• BETWEEN is inclusive at both ends: WHERE published_year BETWEEN 2010 AND 2020.
• LIKE matches text patterns. % means "any run of characters" and _ means "exactly one character": WHERE title LIKE 'The %' finds titles starting with "The ". In SQLite, LIKE ignores case for ASCII letters; in PostgreSQL it is case-sensitive (use ILIKE there).

ORDER BY: sort the result

Without ORDER BY, the database may return rows in any order, and that order can change between runs. If the order matters, say so.

SELECT title, price
FROM books
ORDER BY price DESC;

ASC (ascending, the default) sorts low to high; DESC sorts high to low. You can sort by several columns. The second breaks ties in the first:

SELECT title, author, published_year
FROM books
ORDER BY author ASC, published_year DESC;

Andy Weir's two books appear together, newest first.

LIMIT: only the top rows

LIMIT n keeps the first n rows of the sorted result. Combined with ORDER BY, it answers "top N" questions:

-- The two most expensive books
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 2;

LIMIT works in SQLite, PostgreSQL and MySQL. SQL Server uses SELECT TOP 2 ... instead, and the standard form is FETCH FIRST 2 ROWS ONLY.

How the database reads your query

You write SELECT first, but the database logically processes FROM first, then WHERE, then SELECT, then ORDER BY, then LIMIT. That explains a rule that trips up beginners: in most engines you cannot use a SELECT alias inside WHERE, because WHERE runs before the alias exists. You can use an alias in ORDER BY, because sorting happens after SELECT.

Common mistakes

• Double quotes for text. Use 'Memoir', not "Memoir".
• = NULL. Missing values are never equal to anything. Use IS NULL (covered in module 2).
• Forgetting parentheses when mixing AND and OR.
• Relying on the default order. No ORDER BY means no guaranteed order.
• LIMIT without ORDER BY. "The first 10 rows" is meaningless unless you say first by what.

Quick recap

• SELECT picks columns (* for all, AS to rename, DISTINCT to drop duplicates).
• WHERE filters rows with comparisons, AND/OR/NOT, IN, BETWEEN and LIKE.
• ORDER BY ... ASC|DESC sorts; extra columns break ties.
• LIMIT keeps the top rows of a sorted result.

Try it

Open the Do tab. You will query a small bookshop catalogue: filter by genre and price, then sort the result.