The SELECT Statement

The 24-chapter SQL for Beginners series built the shop database last chapter, so chapter 3 can start reading it. This chapter takes apart SELECT — the statement you will type more often than all the others combined — covering its clause order, projection, aliases, expressions, DISTINCT, and the order in which SQLite actually evaluates the parts you write.

The shape of a statement

Every query is a handful of clauses written in a fixed order. You already met three of them; the rest are coming in the next three chapters:

1. SELECT — the columns and expressions you want back. Written first, evaluated near the end.
2. FROM — the table (or tables) to read.
3. WHERE — which rows qualify. Chapter 4.
4. GROUP BY and HAVING — how to bucket rows and which buckets survive. Chapter 6.
5. ORDER BY and LIMIT — arrangement and size of the final result. Chapter 5.

Keywords and identifiers are case-insensitive — select, Select and SELECT are the same word — and a statement ends at the first semicolon. Comments run to the end of the line with --, and a query may span as many lines as you like:

-- Everyone who signed up so far
SELECT first_name, signup_date -- two columns
FROM customers;

Expected output:

Aarav|2025-11-02
Priya|2025-11-15
Lena|2025-12-01
Marc|2025-12-20
Wei|2026-01-12
Amara|2026-02-14

Format the query for humans and the engine ignores every newline, indent and comment. The only punctuation SQL insists on is the semicolon at the end — leave it out at the sqlite> prompt and you get a ...> continuation line waiting for the rest.

Case works the other way round to what people expect: keywords and identifiers are free with capitalisation — Select First_Name FROM Customers runs — so the style you choose is for the reader. This series writes keywords in capitals and column names in lowercase, and once you have seen a file written that way you will read it twice as fast.

Choosing columns

The columns after SELECT are a projection: you get exactly those, in exactly that order, and nothing else. Asking for three fields keeps the result three fields wide:

SELECT id, first_name, email FROM customers;

Expected output:

1|Aarav|aarav@example.com
2|Priya|priya@example.com
3|Lena|lena@example.com
4|Marc|marc@example.com
5|Wei|wei@example.com
6|Amara|amara@example.com

SELECT * is wonderful for exploring a table you have never seen and dangerous in anything you ship: it couples your query to the table’s current shape, reads columns nobody uses, and breaks the moment someone adds or reorders a column. Name the columns you actually need. Order is part of the contract as well — a report, an API and a spreadsheet importer each read your result by position or by name, so keep the select list stable across releases and let aliases document what each position means.

And a misspelled column is reported at once rather than smuggled away as an empty result:

SELECT first_namee FROM customers;

Expected output:

Parse error: no such column: first_namee
  SELECT first_namee FROM customers;
         ^--- error here

The caret points at the offending name. Errors of this kind happen before the query runs at all — SQLite prepares the statement, checks every column against the schema, and refuses to start — which is why a typo costs you a message rather than a silent zero rows.

Aliases with AS

AS renames an output column for the client that receives it — essential when the real name is a table column such as first_name but the consumer expects something else, and indispensable for computed columns. SQLite lets you drop AS entirely; writing it out costs nothing and reads better:

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

Expected output:

Aarav Sharma
Priya Nair
Lena Fischer
Marc Tremblay
Wei Ling
Amara Ochieng

|| is SQL’s standard string concatenation in SQLite and PostgreSQL; MySQL uses CONCAT(a, b) instead (in MySQL || means logical OR unless you enable a compatibility mode), and SQL Server accepts both. One caveat to file away: the alias exists for the result, so ORDER BY full_name works while WHERE full_name = 'Aarav Sharma' does not — WHERE runs before the select list exists, which chapter 4 demonstrates.

Aliases also shrink the mental load in long expressions: once first_name || ' ' || last_name has a name, nobody has to re-read the concatenation to know what the column holds — a habit that pays off the first time an invoice total appears three joins later.

Expressions in the select list

Anything that computes a value can sit in the select list: arithmetic, functions, and combinations of both. The result is a new column that is never stored in the table:

SELECT name, price, price * 2 AS double_price FROM products;

Expected output:

The Silent Patient|499|998
The Very Hungry Caterpillar|249|498
Clean Code|899|1798
Python Crash Course|749|1498
Matilda|349|698
Robotics for Kids|649|1298

Nothing in products has changed; double_price exists only for these six rows and only for this query. That is worth pausing on: reports, invoices and dashboards are all built from columns like this one, computed on the way out rather than stored on the way in. A useful habit is to name every computed column, because an unnamed expression arrives at your application as price * 2 or Expr1, depending on the driver.

The select list will even evaluate arithmetic with no table in sight, which makes it the fastest way to try a function or check a calculation:

SELECT 2 + 2 AS answer;

Expected output:

4

One row, one column, one value — the shape of every result you will ever get back. Put a real function around the arithmetic and the same pattern becomes a tool. The shop prices its goods in whole rupees, so here is each price with 18% tax added:

SELECT name, price, ROUND(price * 1.18, 2) AS with_tax FROM products;

Expected output:

The Silent Patient|499|588.82
The Very Hungry Caterpillar|249|293.82
Clean Code|899|1060.82
Python Crash Course|749|883.82
Matilda|349|411.82
Robotics for Kids|649|765.82

Nothing is rounded away by accident: ROUND(x, 2) keeps two decimal places, and the alias names the column your invoice will read. Functions live everywhere in SQL — in the select list, in WHERE, in ORDER BY — and chapters 17 and 18 are a tour of the ones you will use daily.

Removing duplicates with DISTINCT

DISTINCT collapses identical rows in the result. Asking which categories the shop carries:

SELECT DISTINCT category FROM products;

Expected output:

Fiction
Kids
Tech

Three rows instead of six, in the order the values were first met — which is another reason to add an explicit ORDER BY when the sequence matters. Crucially, DISTINCT applies to the whole row, not to the first column: every combination of country and city in the customers table is

SELECT DISTINCT country, city FROM customers;

Expected output:

India|Mumbai
India|Delhi
Germany|Berlin
Canada|Toronto
Singapore|Singapore
Kenya|Nairobi

Six rows, because no two pairs repeat — the two Indian customers do not cancel each other out. To deduplicate a single column of a much wider row, select just that column, or reach for COUNT(DISTINCT country) when you want the number instead of the values, as chapter 6 shows. There is no shortcut that means “unique first column”: read the whole select list and you will know exactly which question was asked.

Ordering is a separate decision, too. SQLite returned the categories in the order it first met them, which happened to be Fiction, Kids, Tech — append ORDER BY whenever a fixed sequence matters, because neither GROUP BY nor DISTINCT promises one.

How SQLite evaluates your SELECT

You write the clauses in one order; the engine reads them in another. Understanding the sequence explains most surprises:

1. FROM — fetch the source rows.
2. WHERE — discard rows that fail the filter.
3. GROUP BY — bucket what is left (chapter 6).
4. SELECT — now compute columns and aliases.
5. DISTINCT — collapse duplicate rows.
6. ORDER BY — sort the result (chapter 5).
7. LIMIT — cut it down to size (chapter 5).

Because WHERE runs before SELECT, a WHERE clause may not mention an alias defined in the select list, and because ORDER BY runs last, it may mention both aliases and expressions you never selected. Keep that timeline in mind and half the error messages you will ever see stop being mysterious.

It also explains the writing order. You type SELECT first because that is the question you are asking, but the engine reads FROM first because that is where the data comes from; the two orders never have to agree, and the seven-step list above is the bridge between them.

Common mistakes

• Shipping SELECT * — the column count and order become part of your program’s contract with the table, and one ALTER TABLE later nothing lines up.
• Forgetting the comma — SELECT first_name last_name FROM customers is not two columns; it is first_name renamed to last_name, so you get first names under a last-name header.
• Expecting DISTINCT to deduplicate one column — it judges whole rows, so SELECT DISTINCT country, city keeps every distinct pair.
• Using a select-list alias in WHERE — most engines reject it because the filter is evaluated before the alias exists; filter on the underlying expression instead.
• Forgetting the semicolon — the shell sits at ...> looking frozen; finish the statement with ; or press Ctrl+C.
• Selecting columns you never use — an extra field is extra bytes over the wire and an extra thing your application has to understand; project the three columns you actually render.

Practice checklist

1. Run the four queries from this chapter one after another and confirm each output block matches row for row.
2. Project only id and signup_date from customers, then add last_name in the middle and note how the output changes.
3. Write an alias that turns price into a column named rupees, and predict the result before running it.
4. Show DISTINCT status from orders, then explain why the answer has three rows and not six.
5. Break a query on purpose by removing a comma, read the error message, and fix it.

What's next?

You can pick columns and compute new ones; next you choose which rows come back at all, with the filter clause that sits behind almost every query: Filtering Rows with WHERE.