SELECT What You Want: Projection, Expressions, and Aliases

Every query you write starts with SELECT. It answers one question: which columns do I want back. Once you can bend it to your will you can already do a surprising amount: pick columns, rename them, and compute brand-new ones out of the old. This chapter is SELECT and its close companion FROM, worked all the way through against the bookshop’s books table.

The two clauses every query has

The smallest useful query names two things: the columns you want, and the table they come from.

SELECT title, price FROM books ORDER BY book_id LIMIT 5;

    title     | price
--------------+-------
 Book Title 1 | 37.78
 Book Title 2 | 42.07
 Book Title 3 | 28.54
 Book Title 4 | 34.87
 Book Title 5 | 53.42
(5 rows)

FROM books says where to read rows from. SELECT title, price says which of each row’s columns to keep. That act of keeping some columns and dropping the rest is called projection, and the list after SELECT is the select list. The order you write the columns in is the order they come back in; ask for SELECT price, title and price is the left column. The table doesn’t have an inherent column order you’re stuck with. You choose it, per query.

(ORDER BY and LIMIT are here so the output is small and stable. They get their own chapter. For now, treat them as “give me a tidy five rows.”)

SELECT *, and why real code rarely uses it

There is a wildcard that means “every column”:

SELECT * FROM books ORDER BY book_id LIMIT 3;

 book_id |    title     | author_id |   genre    | price | published
---------+--------------+-----------+------------+-------+------------
       1 | Book Title 1 |         1 | Nonfiction | 37.78 | 2013-08-02
       2 | Book Title 2 |         1 | Sci-Fi     | 42.07 | 2012-04-07
       3 | Book Title 3 |        38 | History    | 28.54 | 2006-05-28
(3 rows)

SELECT * is wonderful at the psql prompt when you’re exploring and want to see everything a table holds. In code that ships, prefer to name your columns. Three reasons, all practical. It pulls only the data you need, instead of dragging every column across the wire. It keeps the result’s shape stable. If someone adds a column to books next year, your SELECT * query silently changes shape, which can break code that reads results by position. And it documents intent, so the next person can see exactly which fields the query depends on. * is a great exploration tool and a poor contract.

Aliases: renaming a column with AS

The name a column comes back under is not fixed. You rename it with AS:

SELECT title AS book, price AS usd FROM books ORDER BY book_id LIMIT 3;

     book     |  usd
--------------+-------
 Book Title 1 | 37.78
 Book Title 2 | 42.07
 Book Title 3 | 28.54

The AS keyword is optional (title book works too), but write it out. The version with AS reads unambiguously, and it saves you from a class of typo where a missing comma turns two columns into one aliased column. Aliases matter most when a column is a computed expression, which by default comes back with an ugly auto-generated name. Give it a real one.

Computing new columns

A select-list entry doesn’t have to be a bare column. It can be an expression built out of columns, literals, operators, and functions, evaluated once per row. Arithmetic is the obvious case:

SELECT title, price, round(price * 0.9, 2) AS sale_price
FROM books ORDER BY book_id LIMIT 5;

    title     | price | sale_price
--------------+-------+------------
 Book Title 1 | 37.78 |      34.00
 Book Title 2 | 42.07 |      37.86
 Book Title 3 | 28.54 |      25.69
 Book Title 4 | 34.87 |      31.38
 Book Title 5 | 53.42 |      48.08

price * 0.9 is computed for each row, then round(…, 2) trims it to two decimals. The books table has no sale_price column anywhere on disk; it exists only in this result, conjured for the length of the query. That is the everyday work of SELECT: deriving the numbers you actually want from the ones the table stores.

Text has operators too. The || operator concatenates strings, and it will coerce a number to text along the way:

SELECT title || ' ($' || price || ')' AS label
FROM books ORDER BY book_id LIMIT 3;

         label
-----------------------
 Book Title 1 ($37.78)
 Book Title 2 ($42.07)
 Book Title 3 ($28.54)

One line-by-line note that trips up beginners: || is standard SQL for concatenation, and Postgres, Oracle, and SQLite all use it. MySQL does not by default, where || means logical OR; there you use the CONCAT() function instead.

An expression needs no table at all. SELECT will happily evaluate constants, which makes it a pocket calculator for testing:

SELECT 2 + 2 AS four, 'ship' || 'shape' AS word;

 four |   word
------+-----------
    4 | shipshape
(1 row)

No FROM, one row out. You’ll use this constantly to check what an expression does before wiring it into a real query.

One sharp edge: you can’t reuse an alias in the same SELECT

An alias you define in the select list is not visible to other expressions in that same list. This looks reasonable and fails:

SELECT price * 0.9 AS sale, sale * 2 FROM books LIMIT 1;

ERROR:  column "sale" does not exist
LINE 1: SELECT price * 0.9 AS sale, sale * 2 FROM books LIMIT 1;
                                    ^

The reason is the logical order the clauses are evaluated in, which is not the order you write them. FROM runs first to produce rows, then the select list is computed, and the aliases only come into existence as that list is projected. So one select-list entry cannot see another’s alias, because they’re all born at the same instant. (You can reference the alias in ORDER BY, which runs later; more on that in the sorting chapter.) The workaround is to repeat the expression, or wrap the query in a subquery so the alias exists in an outer layer. Keep this ordering in your head; it explains a lot of “column does not exist” errors down the line.

Paging: LIMIT, OFFSET, and FETCH FIRST

You rarely want all 600 books at once. LIMIT n caps the rows returned; OFFSET m skips the first m before counting:

SELECT book_id, title FROM books ORDER BY book_id LIMIT 3 OFFSET 3;

 book_id |    title
---------+--------------
       4 | Book Title 4
       5 | Book Title 5
       6 | Book Title 6
(3 rows)

OFFSET 3 LIMIT 3 gives you the second page of three: skip 4, 5, 6… no, skip the first three (1, 2, 3) and take the next three (4, 5, 6). That is the classic pagination idiom, “page N of size K” is LIMIT K OFFSET (N-1)*K.

LIMIT/OFFSET is Postgres, MySQL, and SQLite syntax, but it is not in the SQL standard. The standard spelling is OFFSET … FETCH, which Postgres also supports:

SELECT book_id, title FROM books ORDER BY book_id OFFSET 3 FETCH FIRST 3 ROWS ONLY;

 book_id |    title
---------+--------------
       4 | Book Title 4
       5 | Book Title 5
       6 | Book Title 6
(3 rows)

Same rows, standard syntax. FETCH FIRST … ROWS ONLY is what SQL Server and Oracle understand, so reach for it when you want portable code (SQL Server also has its own TOP). For everyday Postgres work LIMIT is shorter and everyone reads it fine.

One honest caveat about both: paging without a stable ORDER BY is a bug waiting to happen. A query has no guaranteed row order unless you give it one. So LIMIT 3 with no ORDER BY returns some three rows, and “some” can differ between runs. Every paging query needs an ORDER BY on a column (or set of columns) unique enough to break ties. That’s why every example above sorts by book_id first.

Final thoughts

SELECT is projection plus computation: it names the columns you keep and lets you derive new ones with expressions, AS giving each a readable name. Two ideas are worth carrying forward. The select list can invent columns the table never stored. And the clauses run in a logical order that starts with FROM, not with the SELECT you typed first. That order is the key to the whole language, and it’s about to explain the next clause, the one that decides not which columns you get but which rows.

Next: WHERE the rows are — filtering rows with comparisons, AND/OR, BETWEEN, IN, and pattern matching.