SQL / SELECTING ROWS
Choosing, reordering, and skipping columns
Write select lists that name exactly the columns you want, in the order you want them, and know why SELECT * is risky in saved queries.
What you will learn
- Put columns in the select list in the order you want them read out.
- Omit a column from the select list and still filter on it in WHERE.
- Name columns instead of * so ALTER TABLE cannot reshape saved queries.
- Check the result header to confirm column count and order match your list.
Understanding Choosing, reordering, and skipping columns
The list between SELECT and FROM is not a request for permission to look at columns; it is a specification of the result's shape. The engine builds a new, temporary result table with one column per entry in that list, in the order you wrote them. The order used in CREATE TABLE is storage and declaration order, and it has no authority over your output: if the table declares title before author, writing SELECT author, title still puts author in the first position. Relational theory calls this a projection, and the important part of the word is that the result is a different shape from the stored table.
Leaving a column out removes it from the result only, not from the query. WHERE, JOIN, GROUP BY, and ORDER BY all operate on the table's rows, so you can filter on shelf_code, sort by pages, and join on id without any of them appearing in the output. Narrowing the list is not cosmetic either: fewer columns means fewer bytes assembled and sent to the client, and when every column you asked for lives in an index, the engine can answer from the index alone and never touch the row itself. That is why "select only what you need" is advice about work done, not about tidiness.
SELECT * means "whatever columns this table has right now, in declaration order", and that meaning is resolved when the statement is prepared, not when you typed it. So an unchanged query can start returning a different number of columns in a different arrangement after someone runs ALTER TABLE. At an interactive prompt that is convenient; inside a view, a saved report, an application that reads fields by position, or an INSERT ... SELECT, it turns the schema into a moving target. Spelling out the columns freezes the contract, which is the whole reason experienced people stop typing the asterisk.
CREATE TABLE book (
id INTEGER,
title TEXT,
author TEXT,
pages INTEGER,
shelf_code TEXT
);
INSERT INTO book VALUES
(1, 'The Small Room', 'Sarton', 272, 'A3'),
(2, 'Ice', 'Kavan', 158, 'B1'),
(3, 'Cassandra', 'Wolf', 138, 'A3');
-- author before title, even though the table declares title first;
-- id and shelf_code are simply not asked for
SELECT author, title, pages
FROM book;The select list is a projection you control: it decides which columns appear and in what order, independently of how the table was declared.
Worked examples
The same * returning a different shape
Shows that SELECT * is resolved against the schema as it is at that moment, so adding a column changes the result of an unchanged query.
CREATE TABLE part (sku TEXT, qty INTEGER);
INSERT INTO part VALUES ('BOLT-M6', 40), ('NUT-M6', 90);
SELECT * FROM part;
ALTER TABLE part ADD COLUMN bin TEXT;
SELECT * FROM part;Example explained
Line 1The first SELECT * returns two columns because the table has exactly two columns when the statement is prepared.
Line 2ALTER TABLE ADD COLUMN appends bin after the existing columns and leaves it NULL in rows that already existed; it prints nothing itself.
Line 3The second SELECT * is byte-for-byte the same query but now yields three columns, because * expands anew each time.
Line 4SELECT sku, qty FROM part would have returned the identical two columns before and after the ALTER.
One table column, two output positions
Demonstrates that the select list is a list of output positions rather than a set of table columns, so a column may be repeated.
CREATE TABLE reading (day TEXT, celsius REAL);
INSERT INTO reading VALUES ('Mon', 12.5), ('Tue', 9.0);
SELECT celsius, day, celsius FROM reading;Example explained
Line 1celsius appears in positions 1 and 3 because each entry in the list creates its own output column.
Line 2day sits in the middle only because that is where it appears in the list; the table stores it first.
Line 3Two output columns now carry the same name, so client code that fetches fields by name is ambiguous, while positions 1 and 3 are not.
Line 4Nothing about the stored table changed: a select list rearranges the result, never the data.
Filtering on a column you do not select
Shows that skipping a column in the select list hides it from the result but not from the rest of the statement.
CREATE TABLE staff (name TEXT, dept TEXT, salary INTEGER);
INSERT INTO staff VALUES
('Ada', 'ops', 72000),
('Ben', 'ops', 64000),
('Cleo', 'lab', 81000);
SELECT name FROM staff WHERE dept = 'ops';Example explained
Line 1dept is compared in WHERE yet never named after SELECT, so it does not appear in the output.
Line 2The result has exactly one column because the select list has exactly one entry, regardless of the table having three.
Line 3Cleo is absent because filtering removed a row; salary is absent because projection removed a column. Those are two independent decisions.
Line 4Adding dept to the select list would change the shape of the result but not which rows come back.
Important notes
The select list controls columns only. Which rows appear, and in what order, is decided elsewhere: without ORDER BY, no engine promises the row order shown in these outputs.
Reordering names in the select list costs nothing at run time; it is skipping wide TEXT or BLOB columns that actually saves I/O and network bytes.
Common mistakes
Leaving a trailing comma, as in SELECT author, title, FROM book; the parser is still waiting for another column and reports a syntax error at FROM.
Forgetting a comma, as in SELECT author title FROM book; this runs without complaint but returns one column whose header is title and whose values are authors, because title is read as an alias. A silently wrong report is worse than an error.
Building an application or an INSERT ... SELECT on top of SELECT *; the day someone adds a column, positions shift and either the insert fails with a column-count mismatch or values land in the wrong columns.
Try it yourself
Change, predict, then run
In a browser SQL editor, create movie(id INTEGER, title TEXT, director TEXT, minutes INTEGER, rating REAL) with three rows, then write one query that returns director, title, minutes in that order and nothing else. Then run ALTER TABLE movie ADD COLUMN language TEXT and confirm your query's output is unchanged while SELECT * FROM movie gains a column.
Open the SQL workspaceCheck your understanding
A nightly job runs INSERT INTO orders_archive SELECT * FROM orders. Someone adds a column to orders only. What happens the next night?
- Nothing changes, because * matches columns by name so the archive still receives the right values.
- orders_archive automatically gains the new column so the shapes keep matching.
- The copy breaks: * now expands to more columns than orders_archive expects, so the insert errors out or, if counts ever line up again, writes values into the wrong columns.
- The new column is silently dropped, because the destination table's definition decides the shape.
Show answer
SELECT * is expanded against the current schema of orders, and INSERT ... SELECT maps the resulting columns to the target by position, not by name, so an extra source column means a column-count mismatch. The first option is tempting because the names look like they line up, but nothing in this statement compares names; naming the columns in both the INSERT and the SELECT is what makes the copy survive schema changes.