SQL SELECT Basics: Columns, Aliases, DISTINCT and SELECT *

SELECT is the first SQL anyone learns and the statement written most often, which is why its small details matter. A SELECT does not tell the database how to find anything. It describes the shape of the result: which columns, computed how, named what. The engine decides how to fetch it. Read that way, the common advice to avoid SELECT * stops being style and becomes a matter of keeping your code's contract with the database stable.

The problem it solves

A table holds everything known about each row. Most code needs a narrow slice: two columns for a report, one value to check, a computed figure for a screen. Asking for exactly that slice moves less data, makes the code's expectations explicit, and lets the database skip work it would otherwise do.

The rest of the query, filtering with WHERE, sorting with ORDER BY, grouping, all builds on this one clause, and in the logical order of execution SELECT is evaluated late, after the rows have been chosen.

The intuition

A basic query has two parts. FROM cow names the source; SELECT tag, litres lists the columns to keep. The technical name is projection: every row comes through, narrowed to the listed columns.

What the select list can hold:

• Columns, in any order you like, regardless of their order in the table.
• Expressions: litres * 1000, string concatenation, function calls, constants. Each produces one value per row.
• Aliases with AS, which name the output column. An alias belongs to the result, so in standard SQL a WHERE on the same query cannot see it; SQLite is lenient here, PostgreSQL is not.
• DISTINCT, which removes duplicate result rows. It applies to the whole projected row, not one column, and it needs a sort or a hash over the result to do it.
• *, every column in table order, whatever that is at the moment the query runs.

Two facts surprise people. A query without ORDER BY returns rows in whatever order the engine finds convenient; SQLite often returns insertion order on a simple scan, but no engine promises it, so add ORDER BY when order matters, as ORDER BY and LIMIT explains. And from Python, every row is a tuple, even a one-column row, so ('A17',) rather than 'A17'.

SELECT * is fine at a prompt. In application code it ships columns nobody reads, and its result shape changes whenever the table does. Code that unpacks a fixed number of values, or reads columns by position, breaks the day someone adds a column. With joins it adds a second problem: duplicate column names from both tables.

Watch it run

The animation uses the lesson's cow table. A query describes the shape of the answer, not the steps to go and get it. FROM cow names the source: three rows live there, three columns wide. SELECT * would carry every column across the wire, wanted or not. The parlour sheet only needs two, the ear tag and the litres, so the query names them. Row one hands back the tuple ('A17', 24.5), two values, not three. Row two comes back the same way, and the engine chose how to fetch them, not you. Three rows in, three tuples out, and even a one-column result is still a tuple per row. Then the twist: tomorrow somebody adds a vet column, and SELECT * now returns a different shape. The named list is untouched. Less data moves, and the result shape stays stable.

The code

Every query below runs through Python's built-in sqlite3 module, SQLite 3.37 here. The lesson's table, a projection, a one-column result and DISTINCT:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE cow (tag TEXT, breed TEXT, litres REAL);
  INSERT INTO cow VALUES ('A17', 'Jersey', 24.5),
                         ('B03', 'Holstein', 31.0),
                         ('C22', 'Jersey', 19.5);
""")
print(db.execute("SELECT tag, litres FROM cow").fetchall())
# [('A17', 24.5), ('B03', 31.0), ('C22', 19.5)]
print(db.execute("SELECT tag FROM cow").fetchone())          # ('A17',)
print(db.execute("SELECT breed FROM cow").fetchall())
# [('Jersey',), ('Holstein',), ('Jersey',)]
print(db.execute("SELECT DISTINCT breed FROM cow ORDER BY breed").fetchall())
# [('Holstein',), ('Jersey',)]

Expressions, aliases and a constant column. The cursor's description holds the output names, which come from the aliases; SQLite also accepts a SELECT with no FROM at all. sqlite3.Row lets the same row be read by name:

cur = db.execute("SELECT tag, litres * 1000 AS ml, 'parlour 2' AS shed FROM cow ORDER BY litres DESC")
print([d[0] for d in cur.description])     # ['tag', 'ml', 'shed']
print(cur.fetchall())
# [('B03', 31000.0, 'parlour 2'), ('A17', 24500.0, 'parlour 2'), ('C22', 19500.0, 'parlour 2')]
print(db.execute("SELECT 6 * 7 AS answer").fetchone())       # (42,)

db.row_factory = sqlite3.Row               # rows that can also be read by column name
row = db.execute("SELECT tag, litres FROM cow ORDER BY tag").fetchone()
print(row["tag"], row["litres"], tuple(row))                 # A17 24.5 ('A17', 24.5)
db.row_factory = None

The animation's last two frames, for real. A column is added; SELECT * grows from three values to four, the named query does not change, and code that unpacks three values fails:

star_before = db.execute("SELECT * FROM cow").fetchone()
named_before = db.execute("SELECT tag, litres FROM cow").fetchone()
db.execute("ALTER TABLE cow ADD COLUMN vet TEXT")            # tomorrow's change
star_after = db.execute("SELECT * FROM cow").fetchone()
named_after = db.execute("SELECT tag, litres FROM cow").fetchone()
print(len(star_before), len(star_after))                     # 3 4
print(named_before == named_after)                           # True
try:
    tag, breed, litres = star_after                          # code written for 3 columns
except ValueError as e:
    print(type(e).__name__)                                  # ValueError

Finally, 300 seeded random tables, each query checked against the same computation in plain Python: a projection with an expression, DISTINCT against a Python set, and SELECT * against the inserted rows:

import random

rng = random.Random(34)
ok = True
for _ in range(300):
    t = sqlite3.connect(":memory:")
    t.execute("CREATE TABLE r (a INTEGER, b TEXT, c INTEGER)")
    rows = [(rng.randint(-5, 5), rng.choice("xyz"), rng.randint(0, 9)) for _ in range(rng.randint(0, 25))]
    t.executemany("INSERT INTO r VALUES (?, ?, ?)", rows)
    got = t.execute("SELECT c, a * 2 + c AS d FROM r ORDER BY rowid").fetchall()
    ok &= got == [(c, a * 2 + c) for a, b, c in rows]                     # projection
    got = t.execute("SELECT DISTINCT b, c FROM r").fetchall()
    ok &= sorted(got) == sorted({(b, c) for a, b, c in rows})             # DISTINCT
    ok &= t.execute("SELECT * FROM r ORDER BY rowid").fetchall() == rows  # every column
print(ok)                                                                 # True

The SQL cheat sheet collects the rest of the clauses on one page.

The complexity

• Projection: O(rows) for a full scan; narrowing columns does not reduce rows read, but it reduces bytes sent and can let an index answer the query alone.
• Expressions: constant work per row each.
• DISTINCT: a sort, O(r log r), or a hash table, O(r) on average, over the r result rows, plus memory for what has been seen.

Where it goes wrong

• SELECT * in application code. Extra bytes, and a result shape that changes under you.
• Relying on row order without ORDER BY. It holds until the plan changes.
• Using an alias in WHERE. Logically, WHERE runs before SELECT names anything; even where an engine tolerates it, repeat the expression or use a subquery for portable SQL.
• Expecting DISTINCT per column. It deduplicates whole rows.
• Treating a one-column row as a value. Unpack it: (tag,) = row.
• Assuming every dialect allows SELECT without FROM. As of October 2026, SQLite, PostgreSQL and MySQL accept it, while older Oracle releases require FROM DUAL; check your engine.

When it shows up in interviews

In the first minutes of any SQL round: "return these two columns", "list the distinct values", "compute this per row". Interviewers also ask why SELECT * is discouraged, why an alias fails in WHERE, and what order rows come back in, and they notice whether you write ORDER BY without being asked.

How to say it in an interview

"SELECT describes the result I want, not how to fetch it. I list the columns I need, compute expressions per row and name them with AS; the engine chooses the plan. DISTINCT removes duplicate result rows, across all selected columns. I avoid SELECT * in application code because it moves unneeded data and its shape changes when the table changes, and I never rely on row order without ORDER BY."