SQL-01-002-P · Primer — SELECT and Choosing Columns

The idea in one sentence

SELECT is how you ask a table for data — you name the columns you want (or write * for all of them), and SQL hands back a new table containing exactly those columns, optionally renamed to whatever you like with AS.

Why it exists

A table on disk might have thirty columns and a million rows, but the question you're actually asking almost never needs all thirty. When you want a customer's name and email to print on an invoice, you don't want their internal ID, their signup timestamp, their marketing-preference flags, and twenty-six other fields riding along. SELECT exists so you can say precisely which slices of the table you care about right now and get back just those — nothing more.

This act of picking columns is called projection, and it's one of the two fundamental moves in relational databases (the other, filtering rows, is WHERE). Projection matters for more than tidiness: every extra column is more bytes read from disk, more data shipped across the network to your app, and more noise for a human reading the result. Naming exactly what you need makes queries faster, cheaper, and easier to understand — and it makes your code stable, because it no longer silently changes shape when someone adds a column to the table next month.

SELECT also lets you rename columns on the way out with aliases. A raw column called usr_eml or a computed expression like price * quantity is a poor label for a report and a poor key for the app code reading the result. An alias lets the query hand back a clean, meaningful name — email, line_total — without touching the underlying table at all.

How it works

A SELECT statement has two essential parts: the select list (the columns you want, right after the word SELECT) and the FROM clause (the table you want them from). SQL reads the table, keeps only the columns in your select list, and returns the result as a brand-new table. Writing SELECT * is a shortcut meaning "every column, in the table's declared order"; listing columns by name — SELECT first_name, email — is called using named columns, and it lets you choose which columns and control their order in the output. To rename a column in the result you add AS new_name; the keyword AS is optional in most databases, but writing it makes intent obvious. Aliases only relabel the output — the real column in the table keeps its original name. Here's a complete, runnable example (standard SQL that works in SQLite, PostgreSQL, MySQL, and more):

CREATE TABLE customers (
    id         INTEGER,
    first_name TEXT,
    usr_eml    TEXT,
    city       TEXT
);

INSERT INTO customers VALUES
    (1, 'Ada',   '[email protected]',    'London'),
    (2, 'Grace', '[email protected]', 'New York');

-- Project two columns and give them clean output names:
SELECT first_name AS name,
       usr_eml    AS email
FROM customers;

-- Result (a new 2-column table):
-- name  | email
-- ------+----------------
-- Ada   | [email protected]
-- Grace | [email protected]

The one gotcha

SELECT * is convenient in the console but a liability in real code. The moment you save a query into an application, a view, or a report, * becomes a trap. Because it means "whatever columns exist right now," its output silently changes shape the day someone adds, removes, or reorders a column — so code that reads results by position breaks, an INSERT INTO t SELECT * FROM s suddenly mismatches, and you ship columns you never intended (extra bytes, or worse, a newly added sensitive field like password_hash). It's also harder for the next reader, who can't tell what the query returns without going and inspecting the table. Use SELECT * for quick exploration; in anything you keep, list the columns by name. And remember aliases are output-only labels: SELECT usr_eml AS email does not rename the column in the table, and in standard SQL you generally can't reuse that alias back in a WHERE on the same query, because filtering is computed before the output names exist.

See it — how the engine turns a table into your result

flowchart TD
    A["Stored table: customers<br/>id, first_name,<br/>usr_eml, city"] --> B{"Read the<br/>SELECT list"}
    B -->|"SELECT *"| C["Keep ALL columns<br/>in table order"]
    B -->|"named columns"| D["Keep only chosen<br/>columns, your order"]
    D --> E{"Any AS<br/>aliases?"}
    E -->|"yes"| F["Relabel output<br/>columns only"]
    E -->|"no"| G["Use original<br/>column names"]
    C --> H["Return a new table<br/>(the result set)"]
    F --> H
    G --> H

The one-line takeaway

SELECT projects a table down to the columns you name — use named columns (not *) in code you keep, and AS to give the result clean, meaningful names.

Where you'll meet it

You'll meet SELECT in the very first query you ever run and in essentially every query after it — it's the verb that starts almost all reads. You'll reach for named columns the moment you wire a query into application code and want the result to stay the same shape as the schema evolves, and you'll bless aliases the first time you compute something like price * quantity AS line_total and need a name for it, or when two joined tables both have an id and you rename them to tell them apart. You'll feel the SELECT * gotcha the day a teammate adds a column and a "quick" query starts dragging a megabyte of extra data — or exposing a field it shouldn't. And you'll meet all of it in interviews: "write a query to return just these fields," or "why is SELECT * discouraged in production code?" A strong answer names projection, contrasts * with explicit columns for stability and performance, and shows aliases cleaning up the output.

Named concepts: SELECT statement, column projection, SELECT * vs named columns, column aliases