SQL / SELECTING ROWS
Reading columns with SELECT
Read columns out of a table with SELECT ... FROM, and predict the exact shape of the result set before you run the query.
What you will learn
- Write SELECT col1, col2 FROM table; and predict its row and column count first
- Read a result set as a new temporary table, not as the stored rows themselves
- Explain why a plain SELECT returns one output row per source row
- Know what * expands to, and why a saved query should name its columns instead
Understanding Reading columns with SELECT
A read has two halves: FROM names the source of rows, and the select list names what to pull out of each one. The engine walks the source a row at a time and, for every row it finds, builds one row of output holding the values you listed. That is why SELECT name, moons FROM planets returns three rows from a three-row table: the select list changes what each output row contains, never how many rows there are.
What comes back is a fresh result set, a table of its own that lives only as long as the statement, carrying the columns and the column order your select list described. The stored rows are untouched, which is why au can be absent from the result while still sitting in planets. A plain SELECT also makes no promise about row order; the engine hands back rows in whatever order was cheapest to produce, and that order can change as the table grows.
The select list is a list of expressions, not strictly a list of column names, and each expression is evaluated once per row. A bare column name is the simplest expression, meaning read this stored value, while units * unit_price is computed at read time and stored nowhere. The one shorthand is *, which expands to every column of the source in the order the table declares them; that makes it convenient for looking around and risky in a query you keep, because the result's shape then changes whenever the table does.
CREATE TABLE planets (
name text,
moons integer,
au numeric(4,2)
);
INSERT INTO planets (name, moons, au) VALUES
('Mercury', 0, 0.39),
('Earth', 1, 1.00),
('Mars', 2, 1.52);
SELECT name, moons
FROM planets;SELECT ... FROM builds a brand-new result table by evaluating the select list once for every row the FROM clause hands it.
Worked examples
What * actually expands to
Shows that * is shorthand for every column in the order the table declares them, not a fixed list you control.
CREATE TABLE readings (
station text,
taken_on date,
celsius numeric(4,1)
);
INSERT INTO readings VALUES
('north', '2026-01-04', 3.5),
('south', '2026-01-04', 11.0);
SELECT * FROM readings;Example explained
Line 1The INSERT has no column list, so the three values are matched to station, taken_on, celsius by declaration order.
Line 2* follows that same declaration order, which is why the header reads station, taken_on, celsius even though the query never names them.
Line 3celsius is numeric(4,1), so 11.0 keeps its trailing zero; the right-alignment of numbers and left-alignment of text is the client's formatting, not part of the values.
Line 4Add a fourth column to readings tomorrow and this identical query returns a four-column result, which is the reason to name columns in queries you save.
A value that exists only in the result
Shows the select list being evaluated once per row, producing a column that is not stored anywhere in the table.
CREATE TABLE orders (
ref text,
units integer,
unit_price numeric(6,2)
);
INSERT INTO orders (ref, units, unit_price) VALUES
('A-1', 3, 4.50),
('A-2', 10, 1.25);
SELECT ref, units * unit_price FROM orders;Example explained
Line 1units * unit_price runs once per row, so row A-1 computes 3 * 4.50 and row A-2 computes 10 * 1.25; two source rows still give two result rows.
Line 2Numeric multiplication adds the scales, so 10 * 1.25 prints as 12.50 rather than 12.5.
Line 3The computed column is built at read time and stored nowhere, so orders still holds exactly three columns.
Line 4That expression has no name of its own, so PostgreSQL labels the column ?column?; supplying a readable name is what aliases are for.
Important notes
The pipes, dashes and the (3 rows) line are how the psql client draws a result; another client shows the same rows as a grid or as JSON. The result set itself is identical.
A select list can stand alone: SELECT 2 + 2; is valid in PostgreSQL, MySQL and SQLite because the list alone produces one row, while Oracle requires FROM dual.
Common mistakes
Leaving a trailing comma, as in SELECT name, moons, FROM planets; the parser is still waiting for another expression and reports a syntax error at or near "FROM".
Quoting a column name, as in SELECT 'moons' FROM planets; this runs without complaint and returns the literal text moons once per row instead of the numbers, so the query is silently wrong rather than broken.
Assuming rows come back in insertion order because they did the first time; a plain SELECT guarantees no order, so the same query can hand back a different sequence after updates or once the table is large enough to be scanned differently.
Try it yourself
Change, predict, then run
In the editor, create tracks(title text, seconds integer, album text) and insert four rows, then write one query returning just title and seconds. Write down the row count and column count you expect before running it, and check the header and the row-count line against your guess.
Open the SQL workspaceCheck your understanding
Table planets holds 3 rows and 3 columns. You run SELECT name, moons FROM planets; twice. What comes back, and what has happened to planets?
- A 3-row, 2-column result both times, and planets still holds 3 rows and 3 columns
- A 2-row, 2-column result, because the select list also limits how many rows are read
- A 3-row, 2-column result, and the au column has now been dropped from planets
- A 3-row, 3-column result, because SELECT always returns whatever the table stores
Show answer
FROM decides how many rows are read and the select list decides what each output row contains, so three source rows yield three result rows of two columns, and a read leaves storage alone so the second run behaves like the first. Option 2 is tempting because au really is missing from the output, but the output is a separate throwaway table; au is still stored, as SELECT * would show.