SELECT basics

SELECT is the statement you will write more than any other. It reads data and returns a result table — it never changes anything, so you can experiment freely. In this lesson you will choose columns, compute new values with expressions, give results readable names, remove duplicates and limit how many rows come back. All examples run against the bookstore database from the previous lesson.

#Your first query

The smallest useful query names some columns and the table they come from:

SELECT title, price
FROM books;

           title           | price
---------------------------+-------
 A Wizard of Earthsea      | 12.99
 The Left Hand of Darkness | 15.50
 The Dispossessed          | 16.00
 Guards! Guards!           | 10.99
 Small Gods                | 10.99
 Going Postal              | 11.50
 Kindred                   | 14.00
 Parable of the Sower      | 13.25
 Norwegian Wood            | 17.00
 Kafka on the Shore        | 18.50
(10 rows)

Read it as "from the books table, give me the title and price of every row". The result is itself a table: it has columns, rows, and no guaranteed order.

A few syntax rules:

• Keywords (SELECT, FROM) are case-insensitive. Writing them in capitals is a convention that makes queries easier to scan.
• Whitespace and line breaks do not matter. Put each clause on its own line once queries grow.
• A semicolon ends the statement. psql waits for it before running anything.
• -- starts a comment that runs to the end of the line, and /* ... */ comments can span several lines. Use them to explain why a query does something non-obvious.

#SELECT *

SELECT * returns every column in table order. It is perfect for exploring a table in psql (where the \x command toggles an expanded display that prints one column per line, handy for wide rows):

SELECT * FROM customers;

In application code, list the columns you need instead. * fetches data you will not use, and if someone adds or reorders a column later, code that depends on column positions breaks silently.

#Expressions: computing new columns

A SELECT list is not limited to stored columns. Each item can be any expression, evaluated once per row:

SELECT title, price, stock, price * stock AS stock_value
FROM books
LIMIT 4;

           title           | price | stock | stock_value
---------------------------+-------+-------+-------------
 A Wizard of Earthsea      | 12.99 |    14 |      181.86
 The Left Hand of Darkness | 15.50 |     6 |       93.00
 The Dispossessed          | 16.00 |     0 |        0.00
 Guards! Guards!           | 10.99 |    22 |      241.78
(4 rows)

Nothing is stored: stock_value exists only in this result. Expressions can use:

SELECT name || ' <' || email || '>' AS contact,
       upper(name)                  AS shouting,
       length(name)                 AS name_len
FROM customers
LIMIT 3;

             contact              |   shouting   | name_len
----------------------------------+--------------+----------
 Ada Park <[email protected]>       | ADA PARK     |        8
 Ben Okafor <[email protected]>     | BEN OKAFOR   |       10
 Chloe Martin <[email protected]> | CHLOE MARTIN |       12
(3 rows)

You do not even need a table. SELECT 2 + 2; is a valid query that returns one row, which makes psql a handy calculator for testing an expression before you use it.

#The integer division trap

The type of an expression follows the types of its inputs. Dividing two integers produces an integer, and PostgreSQL truncates the fraction:

SELECT 7 / 2       AS int_div,
       7 / 2.0     AS num_div,
       7::numeric / 2 AS cast_div,
       7 % 2       AS remainder;

 int_div |      num_div       |      cast_div      | remainder
---------+--------------------+--------------------+-----------
       3 | 3.5000000000000000 | 3.5000000000000000 |         1
(1 row)

If either side is numeric, the result is numeric. The :: operator casts a value to another type (7::numeric, '2025-01-05'::date). This bites people computing averages or percentages from integer columns such as stock or quantity — cast first, then divide, then round() if you want fewer digits.

#NULL spreads through expressions

Any arithmetic or || concatenation involving NULL produces NULL. Two customers have no city:

SELECT name,
       'Lives in ' || city        AS with_pipes,
       concat('Lives in ', city)  AS with_concat
FROM customers;

     name     |   with_pipes    |   with_concat
--------------+-----------------+-----------------
 Ada Park     | Lives in Leeds  | Lives in Leeds
 Ben Okafor   | Lives in London | Lives in London
 Chloe Martin | (null)          | Lives in
 Dev Raman    | Lives in Leeds  | Lives in Leeds
 Elena Rossi  | Lives in London | Lives in London
 Farid Haddad | (null)          | Lives in
(6 rows)

The concat() function treats NULL as an empty string, while || does not. Neither is wrong; pick deliberately. The next lesson covers NULL and tools like coalesce() in depth.

#CASE: choosing a value per row

CASE is SQL's if/else expression. Conditions are tested top to bottom, and the first match wins:

SELECT title,
       stock,
       CASE
         WHEN stock = 0 THEN 'sold out'
         WHEN stock < 5 THEN 'low'
         ELSE 'in stock'
       END AS availability
FROM books;

           title           | stock | availability
---------------------------+-------+--------------
 A Wizard of Earthsea      |    14 | in stock
 The Left Hand of Darkness |     6 | in stock
 The Dispossessed          |     0 | sold out
 Guards! Guards!           |    22 | in stock
 Small Gods                |     9 | in stock
 Going Postal              |     3 | low
 Kindred                   |    11 | in stock
 Parable of the Sower      |     5 | in stock
 Norwegian Wood            |     8 | in stock
 Kafka on the Shore        |     0 | sold out
(10 rows)

Order matters: a stock of 0 is also < 5, but the = 0 branch is checked first. If no branch matches and there is no ELSE, the result is NULL.

#Aliases

An alias renames a column or table within a single query. Without one, PostgreSQL invents a name for computed columns — usually the unhelpful ?column?:

SELECT title, price * 2 FROM books LIMIT 2;

           title           | ?column?
---------------------------+----------
 A Wizard of Earthsea      |    25.98
 The Left Hand of Darkness |    31.00
(2 rows)

Add AS name to fix it. AS is optional (price * 2 doubled works), but writing it makes the intent obvious.

#Identifiers and quoting

PostgreSQL folds unquoted identifiers to lower case, so SELECT Title FROM Books works fine. Double quotes preserve case and allow spaces:

SELECT title AS "Book Title", price AS Price
FROM books
LIMIT 2;

        Book Title         | price
---------------------------+-------
 A Wizard of Earthsea      | 12.99
 The Left Hand of Darkness | 15.50
(2 rows)

Notice Price came out as price — it was unquoted, so it was folded.

#Table aliases

You can alias tables too. FROM books AS b lets you write b.title. Qualifying columns with a table name or alias is optional when only one table is involved, but becomes essential once you join tables and two of them have a column with the same name:

SELECT b.title, b.published, 2025 - b.published AS age_in_2025
FROM books AS b
LIMIT 3;

           title           | published | age_in_2025
---------------------------+-----------+-------------
 A Wizard of Earthsea      |      1968 |          57
 The Left Hand of Darkness |      1969 |          56
 The Dispossessed          |      1974 |          51
(3 rows)

#DISTINCT: removing duplicate rows

Which genres does the shop carry? Selecting genre returns ten rows, one per book. DISTINCT collapses identical result rows:

SELECT DISTINCT genre FROM books;

  genre
----------
 fantasy
 sci-fi
 literary
(3 rows)

DISTINCT applies to the whole row, not the first column. SELECT DISTINCT genre, author_id returns five rows, one per distinct (genre, author) combination. And NULLs count as equal for this purpose: SELECT DISTINCT city FROM customers returns Leeds, London and a single (null) row.

#LIMIT, OFFSET and row order

LIMIT n returns at most n rows. OFFSET k skips the first k rows. On its own, though, LIMIT returns an arbitrary set of rows, because a table has no order. Pair it with ORDER BY (covered fully in the next lesson) to make it meaningful:

SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

       title        | price
--------------------+-------
 Kafka on the Shore | 18.50
 Norwegian Wood     | 17.00
 The Dispossessed   | 16.00
(3 rows)

LIMIT 3 OFFSET 3 returns the next three (The Left Hand of Darkness, Kindred, Parable of the Sower). The SQL-standard spelling FETCH FIRST 3 ROWS ONLY does the same as LIMIT 3; PostgreSQL accepts both.

#Practice

Using the bookstore database, write one query against order_items that returns, for every row:

1. order_id and book_id,
2. line_total — quantity times unit price,
3. line_total_with_vat — the line total plus 20% VAT, rounded to two decimals,
4. size — the text 'multi' when more than one copy was bought, otherwise 'single'.

Show only the five lines with the highest line_total, largest first. Then answer: what does SELECT quantity / 2 FROM order_items return for a line with quantity 3, and how would you get 1.5?

SELECT order_id,
       book_id,
       quantity * unit_price                  AS line_total,
       round(quantity * unit_price * 1.2, 2)  AS line_total_with_vat,
       CASE WHEN quantity > 1 THEN 'multi' ELSE 'single' END AS size
FROM order_items
ORDER BY line_total DESC
LIMIT 5;

 order_id | book_id | line_total | line_total_with_vat |  size
----------+---------+------------+---------------------+--------
        7 |       1 |      38.97 |               46.76 | multi
        5 |       7 |      28.00 |               33.60 | multi
       10 |       6 |      23.00 |               27.60 | multi
        1 |       4 |      19.98 |               23.98 | multi
        4 |      10 |      18.50 |               22.20 | single
(5 rows)

#Key takeaways

• SELECT columns FROM table returns a new result table; it never modifies data.
• Each item in the select list can be any expression — arithmetic, ||, functions, CASE — evaluated per row.
• Integer divided by integer truncates; cast with ::numeric when you need fractions. NULL in arithmetic or || yields NULL.
• Use AS to name computed columns; single quotes are strings, double quotes are case-sensitive identifiers.
• DISTINCT removes duplicate whole rows. LIMIT/OFFSET are only meaningful together with ORDER BY.