Asking questions: SELECT, WHERE and ORDER BY

By the end you'll know

How to choose columns, rename them, and calculate new ones with SELECT
How to choose rows with WHERE: comparisons, IN, BETWEEN, LIKE and IS NULL
Why = NULL never matches, and why NOT LIKE skips empty values
Why AND is worked out before OR, and how brackets fix it
How to sort with ORDER BY, put NULLs last, and take a few rows with LIMIT
The order a query is worked out in, and the error that shows it

Before you start

You need: Lessons 1 and 2 done, with the larkspur container running on the larkspur-data volume.
Where commands run: inside psql. Open it with docker exec -it larkspur psql -U postgres -d larkspur, and type each query at the larkspur=# prompt. End each with a semicolon.
Quick check: SELECT count(*) FROM towns; should say 12. If it doesn't, reload the county: \q, then Lesson 1's load command, then back into psql.

The shape of a question

Almost every question you'll ask a database has the same three parts:

SQL

SELECT name, population      -- which columns
FROM towns                   -- from which table
WHERE kind = 'city'          -- which rows
ORDER BY population DESC;    -- in what order

Only SELECT and FROM are required. WHERE and ORDER BY are optional, but when they're there, they come in that order. Everything after -- on a line is a comment: psql ignores it.

Think of it as filling in a request slip at a library. Which details you want about each book (the columns), which shelf (the table), which books on it (the rows), and how to stack them when they're brought to you (the order). You fill in the slip, and the librarian does the walking.

SELECT: which columns

Name the columns you want, in the order you want them:

SQL

SELECT name, kind FROM towns;

OUTPUT

     name      |  kind   
---------------+---------
 Ashford       | city
 Ashford       | village
 Brennan Falls | city
 Cedar Hollow  | town
 Dunmore       | town
 Elk Ridge     | village
 Fairview      | city
 Glen Oak      | town
 Harlow        | town
 Juniper       | village
 Kestrel Bay   | city
 Linden        | town
(12 rows)

Rows came back in id order here, but that's luck, not a promise. Without ORDER BY, a database returns rows in whatever order is quickest for it, and that can change. If the order matters, ask for it.

A column can be calculated, and AS gives it a name:

SQL

SELECT name, population / 1000 AS thousands FROM towns;

OUTPUT

     name      | thousands 
---------------+-----------
 Ashford       |        48
 Ashford       |         1
 Brennan Falls |        21
 Cedar Hollow  |         8
 Dunmore       |          
 Elk Ridge     |         2
 Fairview      |        33
 Glen Oak      |        11
 Harlow        |         6
 Juniper       |         1
 Kestrel Bay   |        27
 Linden        |         9
(12 rows)

Two things to notice:

The numbers are rounded down. population is an integer, and an integer divided by an integer stays an integer: 1,904 people is "1" thousand. That's integer division, and it's an easy way to get quietly wrong answers.
Dunmore is still empty. Anything calculated from NULL is NULL. The database doesn't know Dunmore's population, so it doesn't know a thousandth of it either.

Divide by 1000.0 instead, and the answer keeps its fraction:

SQL

SELECT name, population / 1000.0 AS thousands FROM towns WHERE id <= 3;

OUTPUT

     name      |      thousands      
---------------+---------------------
 Ashford       | 48.2100000000000000
 Ashford       |  1.9040000000000000
 Brennan Falls | 21.5440000000000000
(3 rows)

Correct, if a bit long. Lesson 4 shows round() to tidy it.

DISTINCT removes repeated rows from the answer. Which kinds of town are there?

SQL

SELECT DISTINCT kind FROM towns;

OUTPUT

  kind   
---------
 city
 town
 village
(3 rows)

WHERE: which rows

WHERE keeps only the rows where a condition is true. For numbers, the comparisons are the ones you'd expect: =, >, <, >=, <=, and <> for "not equal".

SQL

SELECT name, population FROM towns WHERE population > 20000;

OUTPUT

     name      | population 
---------------+------------
 Ashford       |      48210
 Brennan Falls |      21544
 Fairview      |      33102
 Kestrel Bay   |      27450
(4 rows)

Text goes in single quotes, and has to match exactly:

SQL

SELECT id, name, kind FROM towns WHERE name = 'Ashford';

OUTPUT

 id |  name   |  kind   
----+---------+---------
  1 | Ashford | city
  2 | Ashford | village
(2 rows)

Both Ashfords, as you'd hope. Now the same question in lower case:

SQL

SELECT id, name FROM towns WHERE name = 'ashford';

OUTPUT

 id | name 
----+------
(0 rows)

Nothing. = on text is exact, capital letters included. Not an error, just no rows, which is easy to mistake for "there's no such town". When you don't trust the case, ILIKE compares without it:

SQL

SELECT id, name FROM towns WHERE name ILIKE 'ashford';

OUTPUT

 id |  name   
----+---------
  1 | Ashford
  2 | Ashford
(2 rows)

(Double quotes mean something else in SQL: the name of a column or table, not text. WHERE name = "Ashford" is an error about a column called Ashford.)

Combine conditions with AND, and use IN for "any of these":

SQL

SELECT name, kind FROM towns WHERE kind IN ('village', 'town') AND founded < 1890;

OUTPUT

   name   |  kind   
----------+---------
 Ashford  | village
 Dunmore  | town
 Glen Oak | town
(3 rows)

BETWEEN is a range, and includes both ends:

SQL

SELECT name, founded FROM towns WHERE founded BETWEEN 1880 AND 1900;

OUTPUT

   name   | founded 
----------+---------
 Ashford  |    1888
 Glen Oak |    1884
 Harlow   |    1896
 Linden   |    1890
(4 rows)

Linden, founded in 1890, is in. That's the same as founded >= 1880 AND founded <= 1900.

Dates are written as text in quotes, year first, and compare the way you'd expect. Meetings from March on:

SQL

SELECT id, held_on, body FROM meetings WHERE held_on >= '2026-03-01';

OUTPUT

 id |  held_on   |   body   
----+------------+----------
  4 | 2026-03-09 | council
  6 | 2026-03-17 | council
 10 | 2026-03-03 | council
 20 | 2026-03-10 | council
 27 | 2026-03-04 | council
 30 | 2026-03-23 | planning
(6 rows)

Because held_on is a date column, Postgres turns '2026-03-01' into a date, and compares dates, not text. Always write dates as year-month-day: it's the one form every database reads the same way.

LIKE: patterns

LIKE matches a pattern: % means "any text, or none", and _ means "exactly one character". Towns with an "a" in their name:

SQL

SELECT name FROM towns WHERE name LIKE '%a%';

OUTPUT

     name      
---------------
 Brennan Falls
 Cedar Hollow
 Fairview
 Glen Oak
 Harlow
 Kestrel Bay
(6 rows)

Ashford isn't there. Its "A" is a capital, and LIKE is as exact about case as =. ILIKE '%a%' would include it.

Websites ending in .gov:

SQL

SELECT name, website FROM towns WHERE website LIKE '%.gov';

OUTPUT

     name      |           website            
---------------+------------------------------
 Ashford       | https://www.ashfordcity.gov
 Brennan Falls | https://www.brennanfalls.gov
 Dunmore       | https://www.dunmoretown.gov
 Elk Ridge     | https://www.elkridge.gov
 Fairview      | https://www.fairview.gov
 Glen Oak      | https://www.glenoak.gov
 Harlow        | https://www.harlowtown.gov
 Kestrel Bay   | https://www.kestrelbay.gov
(8 rows)

Eight of twelve. CivicGrid has the same kind of rule: when it repairs a city's website, it only accepts .gov addresses, or a state's own .us ones.

Now the reverse, a real data check: which websites don't start with https://?

SQL

SELECT name, website FROM towns WHERE website NOT LIKE 'https://%';

OUTPUT

     name     |            website             
--------------+--------------------------------
 Cedar Hollow | http://cedarhollow.larkspur.us
(1 row)

Cedar Hollow, still on plain http. It looks complete. It isn't, and the next section shows why.

NULL in WHERE

Dunmore's population is NULL. Ask for towns whose population equals NULL:

SQL

SELECT name FROM towns WHERE population = NULL;

OUTPUT

 name 
------
(0 rows)

No rows, not even Dunmore. NULL means "unknown", and asking whether an unknown number equals another unknown can only be answered "unknown". WHERE keeps a row only when the condition is true, and unknown isn't true. So = NULL never matches anything, and no error tells you so. The right way is IS NULL:

SQL

SELECT name FROM towns WHERE population IS NULL;

OUTPUT

  name   
---------
 Dunmore
(1 row)

The same rule broke the website check. The Ashford village and Juniper have no website at all. "Does nothing not start with https://?" is unknown, so they were left out. To include them, ask for them:

SQL

SELECT name, website FROM towns WHERE website IS NULL OR website NOT LIKE 'https://%';

OUTPUT

     name     |            website             
--------------+--------------------------------
 Ashford      | 
 Cedar Hollow | http://cedarhollow.larkspur.us
 Juniper      | 
(3 rows)

Three towns to fix, not one. Any check that says "not like this" should ask itself what happens to the empty ones. Lesson 8 is all about NULL.

AND before OR

Meetings that are council meetings or planning meetings, with more than 40 people:

SQL

SELECT id, body, attendance FROM meetings
WHERE body = 'council' OR body = 'planning' AND attendance > 40;

OUTPUT

 id |  body   | attendance 
----+---------+------------
  1 | council |         41
  2 | council |         38
  4 | council |         55
  5 | council |          9
  6 | council |         11
...
 28 | council |         12
 29 | council |         15
(24 rows)

(Shortened here: all 24 rows came back.) Meetings of 9 and 11 people, and the one with no attendance at all. AND is worked out before OR, the way multiplying comes before adding in arithmetic. So the database read it as:

SQL

body = 'council' OR (body = 'planning' AND attendance > 40)

"Every council meeting, plus planning meetings over 40". Say what you meant with brackets:

SQL

SELECT id, body, attendance FROM meetings
WHERE (body = 'council' OR body = 'planning') AND attendance > 40;

OUTPUT

 id |  body   | attendance 
----+---------+------------
  1 | council |         41
  4 | council |         55
 19 | council |         44
(3 rows)

Three. Whenever AND and OR are in the same WHERE, use brackets, even when you're sure. The next person to read it won't have to be.

A real question: who is mayor now?

A mayor whose term hasn't ended has no ended date:

SQL

SELECT town_id, name, started, ended FROM leaders
WHERE role = 'mayor' AND ended IS NULL
ORDER BY town_id;

OUTPUT

 town_id |      name      |  started   | ended 
---------+----------------+------------+-------
       1 | Thomas Reyes   | 2022-01-01 | 
       2 | Thomas Reyes   | 2020-05-01 | 
       3 | Dana Ruiz      | 2021-01-01 | 
       4 | Sam Whitfield  | 2023-01-01 | 
       5 | Grace Lin      | 2019-01-01 | 
       5 | Owen Price     | 2023-01-01 | 
       6 | Ivy Chen       | 2022-01-01 | 
       7 | Marcus Bell    | 2020-01-01 | 
       9 | Rosa Diaz      | 2021-01-01 | 
      11 | Ana Torres     | 2023-01-01 | 
      12 | Joy Hart       | 2022-01-01 | 
(11 rows)

Eleven rows for twelve towns. Read it the way Lesson 1 said to, and three things stand out:

Town 5, Dunmore, has two current mayors. Grace Lin's term was never closed when Owen Price started. One town, one mayor; this is the kind of mistake a missed update leaves behind.
Towns 8 and 10 are missing. Glen Oak's last mayor left at the end of 2024, and nobody replaced the record. Juniper has never had a leader at all.
Thomas Reyes is mayor of both Ashfords. That one may be fine: two different people can share a name. Only the town_id tells them apart, which is why the rule is never to identify anyone by name alone.

Then there's Sam Whitfield. Look him up by name:

SQL

SELECT id, name FROM leaders WHERE name = 'Sam Whitfield';

OUTPUT

 id | name 
----+------
(0 rows)

He's in the list above, but = can't find him. Look for names that start with Sam, and measure them:

SQL

SELECT id, name, length(name) FROM leaders WHERE name LIKE 'Sam%';

OUTPUT

 id |      name      | length 
----+----------------+--------
  8 | Sam Whitfield  |     14
(1 row)

"Sam Whitfield" is 13 characters. This one is 14: there's a space at the end, invisible in the output, and enough to make = fail. Real data is full of these, usually from copying and pasting. Lesson 7 fixes it, and Lesson 6 stops it happening again.

One more check, with no role in it at all. A term can't end before it starts:

SQL

SELECT id, name, started, ended FROM leaders WHERE ended < started;

OUTPUT

 id |   name    |  started   |   ended    
----+-----------+------------+------------
 20 | Carl Webb | 2024-01-01 | 2023-06-30
(1 row)

Carl Webb, in Linden. Probably a typo in one of the years. Notice the shape of that query: the answer to a check should be no rows. Any row it returns is a problem. Lesson 10 turns that idea into a set of checks that run every time the data changes, the way CivicGrid's do.

ORDER BY and LIMIT

ORDER BY sorts, smallest first, or biggest first with DESC (descending). LIMIT keeps the first few rows. From Lesson 1, the three biggest towns:

SQL

SELECT name, population FROM towns ORDER BY population DESC LIMIT 3;

OUTPUT

   name   | population 
----------+------------
 Dunmore  |           
 Ashford  |      48210
 Fairview |      33102
(3 rows)

Dunmore first, with no population: Postgres sorts NULL as bigger than any number. Here's the fix promised in Lesson 1, NULLS LAST:

SQL

SELECT name, population FROM towns ORDER BY population DESC NULLS LAST LIMIT 3;

OUTPUT

    name     | population 
-------------+------------
 Ashford     |      48210
 Fairview    |      33102
 Kestrel Bay |      27450
(3 rows)

Sort by more than one column by listing them. The second only decides between rows that tie on the first:

SQL

SELECT name, kind, population FROM towns ORDER BY kind, population DESC;

OUTPUT

     name      |  kind   | population 
---------------+---------+------------
 Ashford       | city    |      48210
 Fairview      | city    |      33102
 Kestrel Bay   | city    |      27450
 Brennan Falls | city    |      21544
 Dunmore       | town    |           
 Glen Oak      | town    |      11876
 Linden        | town    |       9734
 Cedar Hollow  | town    |       8390
 Harlow        | town    |       6045
 Elk Ridge     | village |       2710
 Ashford       | village |       1904
 Juniper       | village |       1288
(12 rows)

Kinds in alphabetical order, and biggest first within each. DESC belongs to the one column it follows: kind is still sorted A to Z. And there's Dunmore again, top of the towns.

OFFSET skips rows before LIMIT takes some. Rows 5 to 8, alphabetically:

SQL

SELECT name FROM towns ORDER BY name LIMIT 4 OFFSET 4;

OUTPUT

   name    
-----------
 Dunmore
 Elk Ridge
 Fairview
 Glen Oak
(4 rows)

That's how a website shows page 2 of a list: page size 4, skip the first page. It only works with an ORDER BY; without one, "the next four" can be different each time.

The order a query is worked out in

You write a query in the order SELECT, FROM, WHERE, ORDER BY. The database works it out in a different order:

FROM: find the table.
WHERE: keep the rows that match.
SELECT: work out the columns, and give them their AS names.
ORDER BY, then LIMIT: sort, and cut.

You can see it happen. Use the name thousands from SELECT in WHERE:

SQL

SELECT name, population / 1000 AS thousands FROM towns WHERE thousands > 20;

OUTPUT

ERROR:  column "thousands" does not exist
LINE 1: ..., population / 1000 AS thousands FROM towns WHERE thousands ...
                                                             ^

When WHERE runs, SELECT hasn't happened yet, so thousands doesn't exist. Write the calculation out again in WHERE (WHERE population / 1000 > 20). But ORDER BY comes after SELECT, so there, the name works:

SQL

SELECT name, population / 1000 AS thousands FROM towns ORDER BY thousands DESC NULLS LAST LIMIT 2;

OUTPUT

   name   | thousands 
----------+-----------
 Ashford  |        48
 Fairview |        33
(2 rows)

Knowing this order explains most of SQL's "why can't I do that?" errors, including several in Lesson 4.

In real systems

CivicGrid's checks are WHERE clauses. "No two cities share a phone number" and "every phone is in the same format" are queries whose right answer is no rows, run after every bulk change, the way you ran ended < started.
LIMIT and OFFSET are how an API pages through results, like CivicGrid's city lists. On big tables, OFFSET gets slow, because the database still has to count past every skipped row. Lesson 9 shows why, and the faster alternative.
ILIKE '%...%' is fine on twelve rows, and slow on millions, because a normal index can't help when the pattern starts with %. Search boxes on big sites use a different tool for it.

Spot the problem

Work out what's wrong before you read the explanation.

The cleanup that missed some. Someone wants every town without a proper .gov website, and runs SELECT name FROM towns WHERE website NOT LIKE '%.gov';. It returns Cedar Hollow and Linden, and they fix both. What did they miss? The Ashford village and Juniper, which have no website at all. NOT LIKE on NULL is unknown, not true, so those rows were quietly left out. The towns most in need of a website were the ones the check couldn't see. The fix is to ask for the empty ones too: WHERE website IS NULL OR website NOT LIKE '%.gov'.

Keep this

To	Write
Choose columns, in order	`SELECT name, kind FROM towns;`
Calculate and name a column	`SELECT population / 1000.0 AS thousands FROM towns;`
Remove repeated rows	`SELECT DISTINCT kind FROM towns;`
Compare	`=`, `<>`, `>`, `<`, `>=`, `<=`
Any of these	`WHERE kind IN ('village', 'town')`
A range, both ends included	`WHERE founded BETWEEN 1880 AND 1900`
A pattern, or one ignoring case	`LIKE '%.gov'`, `ILIKE 'ashford'`
Empty, or not	`IS NULL`, `IS NOT NULL` (never `= NULL`)
Mix AND and OR	`WHERE (a OR b) AND c`, with brackets
Sort, biggest first, empties last	`ORDER BY population DESC NULLS LAST`
Sort by two columns	`ORDER BY kind, population DESC`
A page of rows	`ORDER BY name LIMIT 4 OFFSET 4`
Measure text	`length(name)`

Where you are now

You can ask a table for exactly the columns and rows you want, in the order you want, and you've found six problems in Larkspur County's data with nothing more than WHERE. Lesson 4 adds up instead of listing: count, sum, avg, and GROUP BY, to answer questions like "how many meetings did each town hold?"

Check yourself

WHERE name = 'ashford' returns no rows, but there are two towns called Ashford. Why?
What does SELECT population / 1000 give for a town of 1,904 people?
Why does WHERE population = NULL return no rows, even for Dunmore?
WHERE body = 'council' OR body = 'planning' AND attendance > 40 returned meetings of 9 people. Why?
Which query puts the towns with no population at the end?
Why can ORDER BY use a name made up with AS in SELECT, when WHERE can't?

Key terms

SELECT, FROM, WHERE, ORDER BY: which columns, from which table, which rows, in what order.
Alias: a name given with AS to a column in the answer.
Integer division: dividing whole numbers gives a whole number, rounded down.
DISTINCT: removes repeated rows from the answer.
LIKE and ILIKE: pattern matching, with % for any text and _ for one character. ILIKE ignores case.
IS NULL: the only way to find empty values. = NULL never matches.
Precedence: which part of a condition is worked out first. AND comes before OR.
LIMIT and OFFSET: keep a few rows, after skipping some. Only meaningful with ORDER BY.

Go deeper

The PostgreSQL tutorial chapter Querying a Table (opens in a new tab).
Try: meetings in February with no minutes link. (Two conditions, one with IS NULL. You should get four.)
Try: leaders whose name has a space at the start or the end. (LIKE ' %' OR LIKE '% ', with brackets if you add anything else.)