Querying with SELECT

Table of Contents

1. Asking Questions
2. Choosing Columns and Aliases
3. Filtering with WHERE
4. AND, OR, NOT and Precedence
5. IN and BETWEEN
6. Pattern Matching with LIKE
7. NULL and Three-Valued Logic
8. Sorting with ORDER BY
9. LIMIT and OFFSET
10. DISTINCT
11. Expressions and Functions
12. CASE: If/Else in SQL
13. The Order SQL Really Runs In
14. Saving Your Queries
15. Try It Yourself

1. Asking Questions

So far you have written the structure of a database and put data in it. Now comes the part most people mean when they say "SQL": SELECT, the statement that reads data. It is the DQL of the three categories from chapter 1 (some people count it as part of DML), and it is what you will write 90 percent of the time.

Make sure your tables hold the seed data. From the shopdb/ folder:

docker exec -i shopdb-pg psql -U postgres -d shopdb < seed.sql
docker exec -it shopdb-pg psql -U postgres -d shopdb

The simplest query reads everything from one table:

SELECT * FROM products;

The * means "all columns". It is great for a quick look, and bad in application code: you fetch data you do not use, and your code changes behavior when someone adds a column. In real code, name the columns you need.

A SELECT has a few optional parts that always appear in the same order. Here is the whole shape, and this chapter fills in each piece:

SELECT   columns or expressions
FROM     table
WHERE    row filter
ORDER BY sort order
LIMIT    how many rows;

Keywords are not case-sensitive (select works the same as SELECT), but writing them in capitals and starting each clause on its own line makes queries easy to read. The data inside quotes is case-sensitive, and that matters: category = 'books' finds nothing, because the stored value is 'Books'.

2. Choosing Columns and Aliases

List the columns you want, separated by commas. The result has them in that order:

SELECT name, price FROM products;

You can rename a column in the output with AS. This is called an alias:

SELECT name AS product, price AS unit_price
FROM products
LIMIT 3;

       product       | unit_price
---------------------+------------
 Wireless Mouse      |      15.00
 USB-C Cable         |       5.50
 Mechanical Keyboard |      79.00
(3 rows)

(Without an ORDER BY, the database is free to return rows in any order. On a small, untouched table you will usually get insertion order, but never depend on it. Section 8 covers sorting.)

Aliases do not change the table. They only name things in the result, which matters more once the columns are calculated, as in section 11. If an alias contains spaces or capitals, put it in double quotes: AS "Unit Price".

3. Filtering with WHERE

WHERE keeps only the rows for which a condition is true. These are the comparison operators:

Which products cost under 20, cheapest first?

SELECT name, price
FROM products
WHERE price < 20
ORDER BY price;

      name      | price
----------------+-------
 USB-C Cable    |  5.50
 Coffee Mug     |  6.00
 Notebook Pack  |  9.50
 Wireless Mouse | 15.00
(4 rows)

Strings and numbers compare naturally. Text values go in single quotes, numbers do not:

SELECT name, stock FROM products WHERE stock = 0;

    name   | stock
-----------+-------
 Desk Lamp |     0
(1 row)

Think of WHERE as a function that every row is passed through: the database checks the condition for each row, and keeps it if the answer is true.

4. AND, OR, NOT and Precedence

Combine conditions with AND (both must be true), OR (at least one), and NOT (flip it).

SELECT name, price
FROM products
WHERE category = 'Books' AND price > 10
ORDER BY name;

     name     | price
--------------+-------
 Clean Code   | 28.50
 SQL Cookbook | 32.00
(2 rows)

Now the trap. AND binds tighter than OR, exactly like * binds tighter than + in arithmetic. Suppose you want "Books or Home items that cost under 10". This looks right:

SELECT name, category, price
FROM products
WHERE category = 'Books' OR category = 'Home' AND price < 10
ORDER BY id;

     name      | category | price
---------------+----------+-------
 SQL Cookbook  | Books    | 32.00
 Clean Code    | Books    | 28.50
 Coffee Mug    | Home     |  6.00
 Notebook Pack | Books    |  9.50
(4 rows)

But SQL read it as Books OR (Home AND price < 10), so every book came back no matter the price. Add parentheses to say what you mean:

SELECT name, category, price
FROM products
WHERE (category = 'Books' OR category = 'Home') AND price < 10
ORDER BY id;

     name      | category | price
---------------+----------+-------
 Coffee Mug    | Home     |  6.00
 Notebook Pack | Books    |  9.50
(2 rows)

The rule: whenever you mix AND and OR, use parentheses, even where they are not strictly required. Your future self will thank you.

5. IN and BETWEEN

A list of ORs on the same column is verbose. IN says the same thing shorter:

SELECT name, category
FROM products
WHERE category IN ('Books', 'Home')
ORDER BY id;

     name      | category
---------------+----------
 SQL Cookbook  | Books
 Clean Code    | Books
 Desk Lamp     | Home
 Coffee Mug    | Home
 Notebook Pack | Books
(5 rows)

NOT IN gives the opposite. (Section 7 has a nasty surprise about it.)

BETWEEN checks a range, and both ends are included:

SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 30
ORDER BY price;

      name      | price
----------------+-------
 Wireless Mouse | 15.00
 Desk Lamp      | 22.00
 Clean Code     | 28.50
(3 rows)

It is just shorthand for price >= 10 AND price <= 30. It works on numbers, dates, and text.

6. Pattern Matching with LIKE

Sometimes you do not know the exact text. LIKE matches a pattern with two wildcards:

• % stands for any number of characters, including none.
• _ stands for exactly one character.

SELECT name FROM products WHERE name LIKE 'C%';

    name
------------
 Clean Code
 Coffee Mug
(2 rows)

That finds names starting with C. USB-C Cable is not included because it starts with U. To find a word anywhere in the text, put % on both sides:

SELECT name FROM products WHERE name LIKE '%Cable%';

    name
-------------
 USB-C Cable
(1 row)

LIKE is case-sensitive. Postgres adds ILIKE, the case-insensitive version, which is handy for searching what a user typed:

SELECT name FROM products WHERE name ILIKE '%mouse%';

      name
----------------
 Wireless Mouse
(1 row)

Pattern matching is fine for small tables. Searching large amounts of text needs different tools (full-text search), and a pattern that starts with % cannot use an ordinary index, a point we return to in chapter 11.

7. NULL and Three-Valued Logic

You met NULL in chapter 3: it means "unknown or missing", not zero and not an empty string. In shopdb, Maly Ros has NULL for city. Watch what happens when you ask for her the obvious way:

SELECT name FROM customers WHERE city = NULL;

 name
------
(0 rows)

No error, and no rows. The reason: comparing anything with an unknown value gives an unknown result, not true or false. Even NULL = NULL is unknown, because two unknown things are not known to be equal.

SQL therefore has three truth values: true, false, and unknown. And WHERE keeps a row only when the condition is true. Unknown is dropped, just like false. To test for NULL you need special operators:

SELECT name, city FROM customers WHERE city IS NULL;

   name   | city
----------+------
 Maly Ros |
(1 row)

(psql shows NULL as an empty cell by default.) Use IS NOT NULL for the reverse.

The same logic causes a quieter bug. Which customers do not live in Phnom Penh?

SELECT name, city FROM customers WHERE city <> 'Phnom Penh';

   name    |    city
-----------+------------
 Dara Kim  | Siem Reap
 Vuthy Sok | Battambang
(2 rows)

Maly is missing. Her city is unknown, so "unknown is not Phnom Penh" is itself unknown, and the row is dropped. Whether that is right depends on your question, but it often is not what you meant. Two fixes:

-- be explicit
WHERE city <> 'Phnom Penh' OR city IS NULL

-- or use the NULL-safe comparison
WHERE city IS DISTINCT FROM 'Phnom Penh'

Either one returns Dara, Vuthy, and Maly.

A third trap: NOT IN with a list that contains a NULL returns nothing at all.

SELECT name FROM customers WHERE city NOT IN ('Phnom Penh', NULL);

 name
------
(0 rows)

For each customer, SQL checks city <> 'Phnom Penh' AND city <> NULL. The second part is never true, so the whole thing is never true. This bites people when the list comes from a subquery that happens to contain a NULL (chapter 9 shows the safe alternative, NOT EXISTS).

The summary: never use = NULL, always think about what NULL does in a condition, and when a query "loses" rows, suspect a NULL.

8. Sorting with ORDER BY

Without ORDER BY, row order is not guaranteed. To sort, name the column. Add DESC for descending (ascending is the default):

SELECT name, price FROM products ORDER BY price DESC;

Sort by several columns: later ones break ties in earlier ones.

SELECT category, name, price
FROM products
ORDER BY category, price DESC;

  category   |        name         | price
-------------+---------------------+-------
 Books       | SQL Cookbook        | 32.00
 Books       | Clean Code          | 28.50
 Books       | Notebook Pack       |  9.50
 Electronics | Mechanical Keyboard | 79.00
 Electronics | Wireless Mouse      | 15.00
 Electronics | USB-C Cable         |  5.50
 Home        | Desk Lamp           | 22.00
 Home        | Coffee Mug          |  6.00
(8 rows)

Categories ascend alphabetically, and within each category prices descend. Where do NULLs go? In Postgres, ascending order puts them last and descending puts them first. You can choose:

SELECT name, city FROM customers ORDER BY city NULLS FIRST, name;

Without NULLS FIRST, Maly would appear at the bottom of an ascending sort.

9. LIMIT and OFFSET

LIMIT n returns at most n rows. Combined with ORDER BY it answers "top N" questions: the three most expensive products.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

        name         | price
---------------------+-------
 Mechanical Keyboard | 79.00
 SQL Cookbook        | 32.00
 Clean Code          | 28.50
(3 rows)

OFFSET n skips the first n rows, which is how pages work. Page 2 of a 3-per-page list:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3;

      name      | price
----------------+-------
 Desk Lamp      | 22.00
 Wireless Mouse | 15.00
 Notebook Pack  |  9.50
(3 rows)

Two warnings. First, LIMIT without ORDER BY returns an arbitrary set of rows, so always sort when you paginate, and sort by something unique (add id as a last tie-breaker) so pages never overlap. Second, OFFSET 100000 still makes the database walk past 100,000 rows before returning yours. Fine for small tables, slow for huge ones.

10. DISTINCT

DISTINCT removes duplicate rows from the result. Which cities do our customers live in?

SELECT DISTINCT city FROM customers ORDER BY city;

    city
------------
 Battambang
 Phnom Penh
 Siem Reap

(4 rows)

Phnom Penh appears once, even though two customers live there. The last row is the NULL (shown as an empty line), sorted last because we used the default ascending order. DISTINCT treats all NULLs as the same.

SELECT DISTINCT category FROM products ORDER BY category;

  category
-------------
 Books
 Electronics
 Home
(3 rows)

DISTINCT applies to the whole row of selected columns, not to one column. SELECT DISTINCT category, price removes only rows where both values repeat. If you find yourself adding DISTINCT to make a query "look right", pause: it often hides a mistake, as you will see with joins in chapter 7.

11. Expressions and Functions

The SELECT list is not limited to columns. It can hold any expression: arithmetic, text operations, function calls. Here is a 10 percent price increase, rounded to cents:

SELECT name, price, round(price * 1.10, 2) AS with_tax
FROM products
WHERE id <= 3;

        name         | price | with_tax
---------------------+-------+----------
 Wireless Mouse      | 15.00 |    16.50
 USB-C Cable         |  5.50 |     6.05
 Mechanical Keyboard | 79.00 |    86.90
(3 rows)

How much money is sitting on the shelf for each product? Price times stock. Show the top three:

SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 3;

        name         | stock_value
---------------------+-------------
 Mechanical Keyboard |     1185.00
 Clean Code          |      855.00
 SQL Cookbook        |      800.00
(3 rows)

Notice that ORDER BY can use the alias stock_value. (Section 13 explains why WHERE cannot.)

Some functions you will use all the time:

Two of these deserve a comment. The || operator joins text but returns NULL if either side is NULL, while concat() simply skips NULLs.

coalesce returns its first argument that is not NULL, which makes it the standard way to replace NULL with a default for display:

SELECT name, coalesce(city, 'unknown') AS city
FROM customers
ORDER BY id;

    name    |    city
------------+------------
 Sokha Chan | Phnom Penh
 Dara Kim   | Siem Reap
 Linda Vong | Phnom Penh
 Vuthy Sok  | Battambang
 Maly Ros   | unknown
(5 rows)

Dates and times have their own arithmetic. Subtracting an interval from a timestamp moves it back in time:

SELECT now() - interval '30 days' AS thirty_days_ago;
SELECT current_date;
SELECT name FROM customers WHERE created_at > now() - interval '1 day';

The last query means "customers who signed up in the last day". Dates are a deep topic: chapter 8 uses date_trunc to group orders by month.

You can also run a query with no table at all, as you did in chapter 2 with SELECT 1 + 1. That is handy for trying out a function.

12. CASE: If/Else in SQL

CASE is SQL's if / else if / else. It returns a value, so you use it inside a SELECT. Here we label each product's stock level:

SELECT
  name,
  stock,
  CASE
    WHEN stock = 0 THEN 'out of stock'
    WHEN stock < 20 THEN 'low'
    ELSE 'ok'
  END AS stock_status
FROM products
ORDER BY id;

        name         | stock | stock_status
---------------------+-------+--------------
 Wireless Mouse      |    40 | ok
 USB-C Cable         |   120 | ok
 Mechanical Keyboard |    15 | low
 SQL Cookbook        |    25 | ok
 Clean Code          |    30 | ok
 Desk Lamp           |     0 | out of stock
 Coffee Mug          |    80 | ok
 Notebook Pack       |    60 | ok
(8 rows)

The branches are checked from top to bottom and the first match wins, so the order matters: a stock of 0 is also less than 20, but the first WHEN catches it. If no branch matches and there is no ELSE, the result is NULL. CASE becomes very powerful together with aggregation, which you will see in chapter 8.

13. The Order SQL Really Runs In

You write SELECT first, but the database does not run it first. It logically evaluates a query in this order:

1. FROM: pick the table (later chapters: the joined tables).
2. WHERE: throw away rows that fail the condition.
3. GROUP BY and HAVING: group and filter groups (chapter 8).
4. SELECT: compute the output columns and aliases.
5. DISTINCT: remove duplicates.
6. ORDER BY: sort.
7. LIMIT / OFFSET: cut the result.

This explains several things that otherwise feel arbitrary. Why can ORDER BY use an alias but WHERE cannot? Because the alias is created in step 4, after WHERE has already run:

SELECT name, price * 1.1 AS with_tax
FROM products
WHERE with_tax > 10;

ERROR:  column "with_tax" does not exist

(Postgres adds a LINE and a caret pointing at the problem.) Repeat the expression in the filter, WHERE price * 1.1 > 10, and it works.

It also explains why LIMIT always comes last: the database must sort everything first to know which rows are the "top 3". And it explains why WHERE filters rows before computing anything, so a smaller table reaches the later steps. Keep this order in your head. It is the model that makes the rest of the series, especially GROUP BY and window functions, fit together.

(This is a logical order. The query planner is free to run steps in a different physical order for speed, as long as the result is the same. Chapter 11 shows how to look at its plan.)

14. Saving Your Queries

You will want to keep the good queries. In your shopdb/ folder, make the queries/ directory from chapter 2 hold a file for this chapter, for example queries/05-select.sql, and put your queries in it with comments:

-- queries/05-select.sql

-- Products under 20, cheapest first
SELECT name, price
FROM products
WHERE price < 20
ORDER BY price;

-- Stock status labels
SELECT name, stock,
  CASE WHEN stock = 0 THEN 'out of stock'
       WHEN stock < 20 THEN 'low'
       ELSE 'ok' END AS stock_status
FROM products
ORDER BY id;

Run the whole file whenever you like:

docker exec -i shopdb-pg psql -U postgres -d shopdb < queries/05-select.sql

Unlike schema.sql and seed.sql, these files only read data, so they are safe to run at any time. This is also how real teams share and review SQL: as files in version control.

15. Try It Yourself

Put your answers in queries/05-select.sql.

1. Cheap electronics. List the name and price of every product in the Electronics category that costs less than 20, most expensive first. How many rows do you expect before you run it?
2. Precedence. Write a query for products that are either in Books or cost over 50, and have stock above 20. Write it once without parentheses and once with. Do the results differ? Explain.
3. Pattern hunt. Find all customers whose email starts with a letter between d and m using LIKE or comparison, and all products whose name contains the word code regardless of case.
4. NULL detective. Find customers whose city is missing. Then write "customers not living in Siem Reap" in a way that correctly includes Maly. Why does city <> 'Siem Reap' alone leave her out?
5. Report-style output. Produce one column named label that reads like Wireless Mouse - $15.00 for each product (use || and a cast such as price::text), sorted by price descending, limited to the top five.
6. Order of evaluation. Write a query that uses an alias in WHERE and read the error. Then fix it twice: once by repeating the expression, and once by wrapping the query as SELECT * FROM (...) AS t WHERE ... (a preview of chapter 9).

Where to go next. You can now ask many questions about a single table. But a shop's data lives across several tables: customers place orders, orders contain products. The next chapter builds those missing tables and connects them with primary and foreign keys: Keys and Relationships.