Beyond typing convenience, what does SELECT * cost compared with listing the columns you need?
answer
- costs more than keystrokes
- every column, every row, every time
- bytes on the wire, plus lost options
- the column list is a contract
- ALTER TABLE quietly changes what it returns
basics
~20 sSELECT * projects every column, so the engine reads and ships bytes the caller discards, gives up access paths a narrower projection would allow, and returns a result whose shape silently changes when the table changes.
solid answer
~40 s`SELECT *` is expanded into the full column list of the tables in `FROM`, so the query really does project everything. That costs three ways. **Bytes**: every row carries columns nobody reads, inflating network transfer, server-side buffers, and any intermediate result that must be sorted or buffered. **Access paths**: if a query touched only columns an index already carries, the engine could answer it without visiting the table rows; the star guarantees columns no index carries, so that option disappears. **Contract**: `ALTER TABLE ... ADD COLUMN` changes what the query returns without the query changing, which is how positional result handling, `INSERT ... SELECT *` and views drift. The explicit list costs one line and removes all three. The star is fine for interactive exploration, and inside `EXISTS`, where the select list is disregarded.
go deeper
Be ready to say what the star expands to and give at least two concrete costs — unread bytes shipped per row, and a result set whose shape changes when someone adds a column.
Explain the mechanics: the projection is materialised per row, it widens anything the plan has to buffer, and it decides whether an index could have answered the query without visiting table rows.
Show judgment about where it actually bites in production — wide TEXT columns on a list endpoint, sorts spilling, an added column leaking into an API response — and how you would find those queries.
Frame it as a convention question: how you get explicit projections enforced across a codebase and an ORM layer without turning every query into a hand-maintained column list.
## What the star actually is `SELECT *` is shorthand. When the statement is parsed, the star is replaced by the ordered list of every column of every table in the `FROM` clause, as those tables are defined at that moment. `SELECT t.*` does the same for one table. Nothing about it is lazy or adaptive: the query projects all of those columns and the engine does the work of producing all of them. That matters because the select list — the projection — is one of the few performance levers a query author fully controls. Filters, ordering and joins are usually dictated by what the feature needs; the projection is often larger than the feature needs purely out of habit. ## Cost one: bytes per row Every projected column is materialised for every returned row. Those bytes are copied into the server's output buffers, pushed through the network, and turned into objects by the client driver. A forty-column row where the caller reads three columns is not marginally more expensive — it can be an order of magnitude more bytes, especially when one of the unread columns is a long `TEXT` or a `BLOB`. The same width also follows the rows through the plan. Anything the engine has to buffer — a sort, a hash build, a temporary result — buffers the *whole projected row*, so a wide projection makes those operators consume more memory and reach the point of spilling to temporary storage sooner. ```sql -- ships every column of a wide table SELECT * FROM articles ORDER BY published_at DESC FETCH FIRST 20 ROWS ONLY; -- ships what the list screen renders SELECT id, title, published_at FROM articles ORDER BY published_at DESC FETCH FIRST 20 ROWS ONLY; ``` ## Cost two: access paths you hand back An index stores the indexed columns alongside the row reference. If every column a statement mentions — in `SELECT`, `WHERE`, `ORDER BY` and `GROUP BY` alike — happens to be present in an index the engine is already using, the engine can in principle produce the answer from the index and never visit the table rows. Add one column that is not in the index and it must go to the table once per matching row. `SELECT *` guarantees the second case, because the star always includes columns no index carries. Choosing which index exists is a separate job (and a different discipline); the projection is the half the query author owns, and a query written with an explicit, minimal list is the one that *can* benefit if such an index exists or is added later. ## Cost three: a contract that moves The explicit list pins what the statement returns. The star delegates that decision to whoever next runs `ALTER TABLE`. Adding a column changes the result set of every star query against that table: - application code that reads results by position gets shifted values; - `INSERT INTO target SELECT * FROM source` starts failing on column count, or worse, lines up type-compatible columns in the wrong order; - a view defined with a star does not necessarily follow the base table, so the view and the table drift apart; - a newly added sensitive column is now returned by every existing star query, including ones that feed an API response. None of these are performance problems, and all of them are found in production rather than in review, which is why interviewers treat the explicit list as a habit question rather than a tuning question. ## Where the star is legitimately fine Interactive exploration — you are the consumer and you want to see the row. `EXISTS (SELECT * FROM ...)`, where the subquery's select list is only tested for the existence of a row, not evaluated for values. And `COUNT(*)`, where the star is not a column list at all: it counts rows and reads no column. Inside a derived table or CTE, many optimisers do prune columns the outer query never references, so an inner star is often harmless — but that is an optimiser courtesy, not a guarantee, and it does not survive the moment someone reuses the view or the CTE elsewhere. ## What a good answer sounds like Name the three costs — bytes, access paths, contract — say which one dominates in a given situation, and note that the fix is free. A candidate who only says "it's slower" has the habit but not the reasoning; a candidate who says "the star is a moving contract" has clearly been burned by it.
- Is SELECT * ever the right choice in real code?In interactive exploration, yes. In `EXISTS (SELECT * ...)` it is idiomatic, because the subquery's select list is not evaluated for values. Inside a derived table or CTE it is usually harmless, since optimisers commonly prune unreferenced columns — but that is not guaranteed and does not survive reuse. In anything an application ships, list the columns.
- Does the argument weaken if the table has only four narrow columns?The byte cost does — four small columns are cheap to ship. The contract cost does not: a star on a narrow table still changes meaning the day someone adds a column, and still blocks the engine from answering from an index that covers a subset. Width scales the first problem; it does not remove the others.
- Does COUNT(*) suffer from the same problem?No. The `*` in `COUNT(*)` is not a column list — it is defined to count rows and reads no column value, so there is nothing to widen. The two spellings share a character and nothing else.
Ordering one of everything on the menu because reading the menu felt like effort: the food still gets cooked, carried and paid for, and the order changes meaning the day the kitchen adds a dish.
saying these in an interview costs you the question
- Claims the optimizer just drops columns you do not use
- Says SELECT * is only a style or readability issue
- Thinks the cost is only network bytes
- Believes fewer columns means fewer rows scanned
- Says it is fine because the table is small today