What exactly does SELECT * expand to, and what happens when two joined tables share a column name?

## What the asterisk means `SELECT *` is not a runtime feature; it is a textual shorthand that the engine expands while analysing the statement. It expands to a reference to **every column of every table expression in the FROM clause**, at the moment the statement is compiled. Two rules fix the resulting order: 1. tables (or derived tables, or CTEs) in the order they appear in `FROM`; 2. within each table, the columns in the order the catalogue records them — the order they were declared in `CREATE TABLE`, with later `ALTER TABLE ... ADD COLUMN` columns normally appended at the end. The order is not alphabetical, not primary-key-first, and not "index order". It is definition order, which is exactly why it can change under you. ## The qualified form `t.*` expands to the columns of just that one table expression, using its alias if it has one. That gives you a middle ground: ```sql SELECT o.*, c.name AS customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id; ``` Here you get all of `orders` plus a single, deliberately named column from `customers`. Per the standard's grammar, an unqualified `*` must stand alone as the whole select list, while `t.*` is one item among others. In practice PostgreSQL, MySQL and SQLite also accept `SELECT *, something_else`, so a query that works locally may not be strictly portable. ## Duplicate column names across a join The expansion does not deduplicate, rename, or coalesce anything. If both joined tables define `customer_id`, the result has two columns named `customer_id`, and if both define `created_at` you get two of those as well. SQL allows a result set to have duplicate column names — the columns are distinguished by position, not by name. That is where application code breaks. JDBC's `getString("created_at")`, a Python `DictCursor`, a pandas frame built from the result — all address columns by name, and each has its own rule for which duplicate wins (usually the first, sometimes the last, sometimes with a silent rename). The symptom is not an error; it is a value quietly coming from the wrong table. The fix is to stop asking for the star and to list and alias the columns you actually want: ```sql SELECT o.order_id, o.created_at AS order_created_at, c.created_at AS customer_created_at, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id; ``` ## Why the star is fragile even without a join Because expansion happens against the catalogue as it stands when the statement is compiled, adding a column to a table changes the shape of every `SELECT *` against it: an extra column appears, usually at the end. Code that reads results positionally (`row[3]`) now reads the wrong thing, and code that maps a result set onto a fixed structure may fail outright. Explicit column lists make the contract between the query and its caller visible in the query text. ## A related asterisk that is not this one The `*` inside `COUNT(*)` is a different piece of grammar entirely — it is not a select list and it does not expand to columns; it simply means "count rows". Do not reason about it using the rules above. ## When the star is fine Interactive exploration, a quick look at a small table, and `SELECT * FROM some_cte` where the CTE already defines a deliberate, narrow column list are all reasonable. The star is a problem when it crosses a boundary — into application code, into a saved report, into anything whose callers you cannot re-read. ## Scope note There are also cost-side arguments about `SELECT *` — extra column transfer, losing an index-only access path — and hazards specific to view definitions and `INSERT ... SELECT`. Those belong to the access-path and schema discussions. The point here is narrower and purely about meaning: what the statement expands to, in what order, and what names come back.