SELECT, WHERE & ORDER BY

Three clauses do most of the work in every SQL query you will ever write:

SELECT name, salary   -- which columns you want
FROM employees        -- which table
WHERE salary > 700000 -- which rows to keep
ORDER BY salary DESC  -- how to sort them
LIMIT 5;              -- how many to return

Read that as one sentence: "select the name and salary columns from employees, keep rows where salary is over 700000, sort them highest-first, and return five." Every query in this course is some subset of that shape. The rest of the lesson covers what each clause does — and why the order you write them is not the order the database runs them.

flowchart LR
  F["FROM employees"] --> W["WHERE keeps matching rows"]
  W --> S["SELECT picks columns"]
  S --> O["ORDER BY sorts"]
  O --> L["LIMIT trims"]
  style W fill:#0e7490,color:#fff
  style O fill:#0e7490,color:#fff

How the database reads your query: pick the table, keep matching rows, choose columns, then sort.

The database runs your clauses in a fixed order

You write SELECT first, but the database evaluates it almost last. It starts at FROM to choose the table, then WHERE removes the rows that fail the condition, then ORDER BY sorts the survivors, and only then does SELECT pick which columns to return — with LIMIT trimming that final list.

Keeping that order in mind prevents a recurring confusion: WHERE filters rows before SELECT chooses columns, so the two operate at different stages. A filter talks about whole rows; the column list talks about what to show from the rows that survived. Mixing them up is the most common beginner mistake, so we treat them as separate steps below.

Filter and sort in one query — change the number or the role and run it again.

SELECT name, role, salary
FROM employees
WHERE salary > 700000
ORDER BY salary DESC;

Filtering rows with WHERE

WHERE keeps the rows you want and quietly discards the rest. Compare with =, !=, <, >, <=, and >=. Text values are wrapped in single quotes — WHERE role = 'Engineer' — while numbers are bare: WHERE salary > 700000. Forgetting the quotes around text is the most common syntax error in a first query, and SQLite's error for it looks more frightening than it is.

Combine conditions with AND, where both sides must hold, and OR, where either side is enough. WHERE role = 'Engineer' AND salary > 700000 means someone who is an engineer and earns over the threshold. Swap in OR and you widen the result to anyone matching either side.

flowchart LR
  R["All rows"] --> F1{"role = 'Engineer'?"}
  F1 -->|yes| F2{"salary > 700000?"}
  F2 -->|yes| K["kept by AND"]
  F1 -->|no| D["dropped"]
  F2 -->|no| D
  style K fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff

AND is two gates in a row — a row must pass every one. OR would be gates side by side, where passing any one is enough.

Both conditions must hold: Engineering employees paid over 700000.

SELECT name, role, salary
FROM employees
WHERE department_id = 1 AND salary > 700000
ORDER BY salary DESC;

Mind the precedence: AND binds tighter than OR

When you mix AND and OR in one WHERE, AND is evaluated first, just as multiplication comes before addition. WHERE role = 'Engineer' OR role = 'Researcher' AND salary > 700000 does not mean "engineers or researchers who earn over 700000" — it means "every engineer, plus any researcher earning over 700000."

The cure is to add parentheses whenever you mix the two. Writing WHERE (role = 'Engineer' OR role = 'Researcher') AND salary > 700000 says exactly what you mean, in any order, and reads back to the next person without guesswork. Parentheses cost nothing; use them freely.

Sorting with ORDER BY

Left to itself, a query returns rows in no guaranteed order — whatever order the engine happened to find them in, which can change between runs. Any time the order of the answer matters, say so with ORDER BY. Add ASC for ascending (the default, smallest first) or DESC for descending (largest first). Dates and text sort too: ORDER BY hired_on DESC lists the newest hires first, because dates stored as YYYY-MM-DD text sort chronologically.

You may sort by several columns, and ties in the first are broken by the second. ORDER BY department_id, salary DESC groups everyone by department and then ranks them by pay within each group.

flowchart LR
  U[("Unsorted rows")] --> O["ORDER BY salary DESC<br/>arranges them"]
  O --> SR["Sorted list"]
  SR --> L["LIMIT 3<br/>keeps the top slice"]
  L --> TOP["Top 3 by salary"]
  style TOP fill:#0e7490,color:#fff
  style U fill:#1e293b,color:#fff

ORDER BY arranges the rows; LIMIT keeps only a slice off the top. Together they answer 'the highest three'.

The five most recent hires: sort by date descending, then keep five.

SELECT name, hired_on
FROM employees
ORDER BY hired_on DESC
LIMIT 5;

LIMIT: keep only a slice

LIMIT caps how many rows come back, which is how you turn a sorted list into a top-N answer. Pair it with ORDER BY and you can ask for the three highest salaries, the five newest hires, or the cheapest project — sort so the rows you want sit on top, then cut everything below them off.

On its own, LIMIT is rarely meaningful, because without an explicit sort the rows it happens to trim are arbitrary. The habit to build is the combination: sort first, then limit. That pairing answers most best-and-worst questions in one short query, and you will use it constantly.

Exercise

List the name and salary of every employee who earns more than 600000, sorted from highest to lowest salary.

SELECT name, salary
FROM employees
-- add your WHERE and ORDER BY here
;

SELECT name, salary FROM employees WHERE salary > 600000 ORDER BY salary DESC;

Exercise

Find the employee who has no department — return their name and role. (Remember what the callout said about NULL.)

SELECT name, role
FROM employees
-- how do you test for a missing value?
;

SELECT name, role FROM employees WHERE department_id IS NULL;

Exercise

Return the 3 most recently hired employees — their name and hired_on, newest first.

SELECT name, hired_on
FROM employees
;

SELECT name, hired_on FROM employees ORDER BY hired_on DESC LIMIT 3;

Exercise

This query is meant to find the department that has no city, but city = NULL matches nothing — run it and you get an empty result. Fix it so it actually returns the Research department.

SELECT name
FROM departments
WHERE city = NULL
;

SELECT name FROM departments WHERE city IS NULL;

Exercise

Return the name and salary of the three lowest-paid employees who earn at least 450000, from lowest to highest. You will need to combine a filter, a sort, and a limit.

SELECT name, salary
FROM employees
;

SELECT name, salary FROM employees WHERE salary >= 450000 ORDER BY salary ASC LIMIT 3;

Recap

The engine evaluates clauses in a fixed order: FROM, then WHERE, then ORDER BY, then SELECT, then LIMIT — not the order you write them in.
WHERE filters rows with comparisons; text is quoted, numbers are not.
AND needs both conditions; OR needs either; AND binds tighter, so parenthesise whenever you mix them.
Missing values are NULL — compare with IS NULL, never = NULL.
ORDER BY sorts (ASC/DESC); LIMIT slices. Sort first, then limit, for any top-N question.

Next you will make SELECT do more than copy columns — computing new values on the fly and renaming them with aliases.

The database runs your clauses in a fixed order

Filtering rows with WHERE

Mind the precedence: AND binds tighter than OR

Sorting with ORDER BY

LIMIT: keep only a slice

Recap

Checkpoint quiz

Go deeper — technical resources