SELECT Basics

"The select list is where you say what you mean. Everything else in the query is about which rows; this is about which facts." — a database teacher's first rule

In Part I you met SELECT the way you meet a city from a train window — enough to recognize it, not enough to live there. You ran SELECT * FROM customers and saw rows appear. That was the tease. This chapter is the depth: SELECT is the single most-used statement in all of SQL, the one you will type ten thousand times in your career, and almost every engineer uses only a fraction of what it can do.

The heart of SELECT is one deceptively small idea — the select list, the comma-separated things you ask for between SELECT and FROM. It answers which columns and computed values do I want back? Filtering rows, sorting, grouping — those come later and get their own chapters. Here we stay laser-focused on the projection: naming columns, renaming them, computing new ones, and stripping duplicates. Master this and every later query reads more clearly, runs a little leaner, and breaks less often in production.

By the end you'll know why SELECT * is a code smell in shipped systems, how to build derived columns with expressions, why SQLite spells string concatenation || and not +, and the neat trick of running SELECT with no table at all as a pocket calculator.

Projection: choosing your columns

The formal name for "which columns come back" is projection. When you write the select list, you are projecting the table down to just the fields you care about. Start with the most common form — naming columns explicitly:

Two columns, five rows. You asked for name and city, and that is exactly what came back — not id, not age. The select list is a filter for columns the same way WHERE is a filter for rows.

The order you list columns is the order they appear in the result. The table stores them as id, name, city, age, but the query decides the layout:

city now leads, then name, then age. The stored order is irrelevant; the select list is the contract for what the caller receives.

Why SELECT * is a smell in production

SELECT * is perfect for exploring at a prompt — quick, no typing, shows you everything. In application code it's a liability, and reviewers flag it for good reasons:

The rule most teams settle on: SELECT * at the console, explicit columns in code. Type the names. Your future self, debugging at 2 a.m., will thank you.

Renaming columns with AS

By default a column comes back labeled with its source name. Often you want a friendlier or clearer label — especially for computed columns, which otherwise get an ugly auto-generated name. That's what aliasing with AS is for:

The data is identical; only the headers change. Aliases matter more than cosmetics: they name computed values (next section), they resolve clashes when two joined tables share a column name, and they become the handle your application code reads by (row["customer"]).

The AS keyword is optional — name customer works too — but write it. SELECT name city FROM customers looks like a typo for SELECT name, city; the explicit AS removes all doubt. If an alias contains spaces, wrap it in double quotes: AS "Full Name".

Computed and derived columns

Here is where the select list stops being a mere picker and becomes an expression engine. Anything that evaluates to a value can go in the select list — arithmetic, function calls, literals, concatenations. The result is a derived column: it isn't stored anywhere, it's computed per row at query time.

Our orders.amount is stored as an INTEGER in kobo (the smallest currency unit — a common real-world practice, since floating-point money is a bug factory). To display naira, divide by 100:

Notice the 100.0, not 100. SQLite does integer division when both operands are integers: amount / 100 would throw away the fractional part. Writing 100.0 forces real-number division. This is a classic trap — 250000 / 100 and 250000 / 100.0 both look right until a value like 3050 kobo reveals the truncation.

You can chain operations and round the result:

Each row gets its own computed naira and a 7.5% VAT figure — none of it stored, all of it derived on the fly. The select list just did real work.

String concatenation — mind the dialect

Joining text is where SQL dialects openly disagree, and it trips up engineers moving between databases. In SQLite (and PostgreSQL, and the SQL standard) the concatenation operator is ||:

Ada from Lagos, Ada (30) — and note SQLite happily coerces the integer age into text inside the concatenation. That convenience is not portable, so be deliberate.

The dialect differences are worth memorizing, because using the wrong one is an instant error at work:

The one that burns people most: in MySQL, || is boolean OR, and 'a' + 'b' tries to add the strings as numbers (yielding 0). Reach for CONCAT() there. Because our playground is SQLite, every runnable block here uses ||.

Literals and functions in the select list

The select list can hold plain constant values (literals) alongside real columns. A literal produces the same value in every row — handy for tagging rows or shaping output for the caller:

status and country are constants stamped onto every row; is_senior is a boolean expression — SQLite has no real boolean type, so it returns 1 for true and 0 for false (a type-affinity quirk worth remembering).

Scalar functions transform a value per row. A few you'll use constantly:

UPPER('Ada') → ADA, LENGTH('Ada') → 3, SUBSTR('Lagos', 1, 3) → Lag. These are scalar functions: one input row, one output value. (Contrast with aggregate functions like COUNT and SUM, which collapse many rows into one — those get their own chapter.)

DISTINCT: removing duplicate rows

Sometimes you want the set of values, not every occurrence. DISTINCT removes duplicate rows from the result. Our five customers live in only three cities:

Three rows back — Lagos, Abuja, Kano — even though two customers each live in Lagos and Abuja. The key subtlety: DISTINCT dedupes across the entire select list, not one column. It looks at the whole projected row and drops exact repeats. Two orders are Book/3000 and two are Pen/500, so:

Six orders collapse to four distinct (product, amount) pairs. Add another column and rows that were duplicates may become unique again:

Now just the product names — Book, Pen, Laptop, Chair — four rows, because the distinctness is over product alone; the two Book orders and two Pen orders each fold into one. The deduping key is exactly the columns you selected. Change the select list, and you change what "duplicate" means.

SELECT with no table: SQL as a calculator

A SELECT doesn't actually need a FROM. Without one, it evaluates the select-list expressions exactly once and returns a single row — a scratchpad for computation, testing a function, or checking your engine's behavior:

One row: 4, 3, 3.3333…, 3.1429. This is the fastest way to prove a dialect quirk to yourself — the 10 / 3 versus 10 / 3.0 gap above is the integer-division trap made visible in two adjacent columns. Use SELECT sans FROM whenever you want to sanity-check an expression before dropping it into a real query.

Projection vs. the rest of the query

Keep the mental model clean. A full query has clauses, and the select list is only the projection step:

Everything in this chapter lives in that first row. The select list decides what each surviving row looks like; the other clauses decide which rows survive and in what order. Confusing the two is the root of many beginner mistakes — like trying to filter with the select list, or expecting DISTINCT to change which rows exist rather than which are duplicated.

Common Mistakes

• SELECT * in application code. Great at the console, fragile in production — schema drift, wasted I/O, dead covering indexes. Name the columns you need.
• Integer division surprise. amount / 100 truncates; write amount / 100.0 (or CAST(amount AS REAL) / 100) when you want a fraction.
• Wrong concat operator across dialects. || in SQLite/Postgres, but CONCAT() in MySQL (where || is OR) and + in SQL Server. Using the wrong one fails loudly or, worse, silently returns 0.
• Forgetting to alias computed columns. amount / 100.0 with no AS gets an ugly auto-name that your code can't reliably reference. Always alias derived columns.
• Misreading DISTINCT. It dedupes the whole projected row, not a single column. SELECT DISTINCT a, b keeps every unique (a, b) pair.
• Using DISTINCT to paper over a bad join. If duplicates appear, understand why before sweeping them away.

Summary

• The select list (the part between SELECT and FROM) performs projection — it decides which columns and computed values come back, and in what order.
• SELECT * is fine for exploring but a smell in production code: fragile to schema changes, wasteful, index-defeating, and intent-hiding. Name your columns.
• AS aliases rename columns and, crucially, name computed ones; always alias derived values.
• The select list is an expression engine: arithmetic, literals, boolean tests, scalar functions, and concatenation all produce derived columns computed per row.
• SQLite concatenates with || — MySQL uses CONCAT() (its || is OR), SQL Server uses +. Watch integer division too: use / 100.0, not / 100.
• DISTINCT dedupes across the entire projected row, not one column; treat it as a signal to check for a deeper cause of duplicates.
• SELECT needs no FROM — a one-row scratchpad for computing and proving expressions.

Next we add the other half of a query: the WHERE clause, where you stop taking every row and start asking precise questions of your data — comparisons, ranges, IN, LIKE, and the three-valued logic of NULL.