SELECT *: Why It Gets Avoided in Production Code

1. Unnecessary network and I/O overhead from superfluous columns

The most obvious downside of SELECT * is that the database reads every single column of a table and transfers it over the connection to the client, regardless of whether the application actually needs it. For a table with a large text column, say a product description or a serialized JSON blob, a query that only actually needs the ID and the name can end up transferring a multiple of the data volume it truly requires.

That overhead adds up noticeably for queries that run frequently: more network bandwidth, more serialization work on the database side, more deserialization on the application side, and more memory consumption for objects that are never used. For a single query on a small table it's barely measurable, for thousands of queries per second on a wide table it becomes a genuine cost factor.

It weighs especially heavily on database I/O itself: if a row contains columns that aren't part of the queried index, the database has to read the actual table row in addition to the index structure, a so-called heap access. That extra I/O operation disappears entirely when only the columns actually needed, already contained in the index, are requested.

-- Also transfers large, unused columns like description and metadata
SELECT * FROM products WHERE category_id = 42;

-- Transfers only the columns actually needed
SELECT id, name, price FROM products WHERE category_id = 42;

2. How schema changes silently break application code with SELECT *

A subtler but more dangerous problem only shows up with a later schema change. If a new column gets added to the table, SELECT * immediately returns an extra field, without any change to the SQL code itself. Application code that processes the result row by position instead of by column name, common in many older data access layers or with manual array access, shifts entirely as a result and suddenly reads the wrong values into the wrong fields, often without an immediate error.

Even trickier is the reverse case: if a column that application code expects by name gets removed or renamed, the access only fails at runtime, when that particular code path actually executes, potentially weeks after the actual schema change and far away from where the change happened. An explicit column list would have surfaced that error immediately and locally at the next deployment, because the query itself fails instead of silently returning wrong data.

This problem intensifies in microservice architectures where multiple services read the same database or table. A team that adds or removes a column often has no complete picture of which other services are implicitly affected via SELECT *, which makes schema changes needlessly risky.

-- Application code reads columns by index position:
-- row[0] = id, row[1] = name, row[2] = email
SELECT * FROM users WHERE id = 7;

-- If "phone" gets inserted as a new column BEFORE "email",
-- the phone number suddenly lands in row[2] instead of the email address

-- An explicit list stays stable regardless of column order
SELECT id, name, email FROM users WHERE id = 7;

3. Blocked covering-index usage as a hidden performance loss

A covering index contains every column a query needs, so the database can serve the result entirely from the index itself, without having to additionally read the actual table row. That saves exactly the heap access described above, and can speed up a query by an order of magnitude, especially for tables whose rows no longer fit entirely in the memory cache.

SELECT * practically always prevents this optimization, because it implicitly requests every column of the table. Even if an index contains every column relevant to the WHERE clause, the database still has to fetch the full row as soon as even a single additional column is requested that isn't part of the index, and with SELECT * that's practically always the case.

An explicit column list, by contrast, allows you to deliberately design indexes that exactly cover the columns needed by a frequently executed query. That relationship between query design and index design becomes practically impossible with SELECT *, because the set of needed columns simply isn't visible to the database, it always has to assume all of them.

-- Covering index for a frequent query
CREATE INDEX idx_orders_customer_covering
  ON orders(customer_id, status, total_cents);

-- Can be served entirely from the index, no heap access
SELECT customer_id, status, total_cents
FROM orders WHERE customer_id = 501;

-- Forces an extra heap access despite the matching index,
-- because additional columns like shipping_address must be fetched
SELECT * FROM orders WHERE customer_id = 501;

4. Implicit contracts and type surprises caused by SELECT *

Another, often overlooked problem concerns type safety on the application side. In statically typed languages with code generation from the database schema, say via ORM tools, an explicit column list produces a clear, checkable result object. SELECT * instead produces a result object whose shape implicitly depends on the current table schema, which invalidates compile-time checks if the schema changes without the generated code being regenerated.

UNION queries across multiple tables using SELECT * carry an additional risk: both tables must supply exactly the same number of columns with compatible types in the same order. If one of the two tables gains a new column while the other stays unchanged, the UNION query fails with a column count mismatch error, an error that an explicit, deliberately aligned column list would have ruled out from the start.

Finally, SELECT * also makes code harder to read and understand during review: an explicit column list immediately documents what data a function actually needs, while SELECT * obscures that information and requires a look at the schema to understand what the query actually returns.

5. SELECT * with joins: duplicate and ambiguous column names

For a query across multiple tables, the SELECT * problem gets worse. If two joined tables share a column name, say both have a column id or created_at, SELECT * returns both columns under the same name. Many data access layers that map the result into an associative array or an object with named fields silently overwrite the first column's value with the second column's value, without any error.

That behavior is especially insidious because it depends on the specific order of the tables in the join and the internal processing of the database driver, and can therefore differ between database systems or even driver versions. A test that stays green locally can suddenly return the wrong id field in a different environment, a classic case of a hard-to-reproduce production bug.

An explicit column list with table aliases and, where needed, individual column aliases fundamentally solves this problem: order.id AS order_id and customer.id AS customer_id are unambiguously named and can never be confused with each other, regardless of join order or the database driver in use.

-- Both tables have an "id" column: result is ambiguous
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Unambiguously named, regardless of join order or driver
SELECT o.id AS order_id, c.id AS customer_id, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id;

6. Legitimate exceptions where SELECT * stays acceptable

Not every use of SELECT * is a mistake. For ad-hoc debugging in an interactive database console, when a developer wants to quickly inspect the contents of an unfamiliar table, the asterisk is the pragmatic choice, because neither performance nor long-term maintainability matter here, the query runs once and gets discarded.

For EXISTS subqueries, where the database optimizer recognizes anyway that the concrete column list is irrelevant to the result because only the existence of a row is being checked, SELECT 1 or SELECT * are practically equivalent, since modern optimizers ignore the column list in that context and transfer no real data.

A third legitimate case is a generic backup, replication, or migration routine whose entire purpose is to copy truly every column of a table unchanged. Here SELECT * isn't just acceptable, it's arguably the more correct choice, because an explicit list would exactly miss a future new column that was actually meant to be copied along.

7. A practical linting and review strategy against SELECT *

Static SQL linters reliably detect SELECT * as a pattern and can be wired into CI pipelines as a mandatory check that blocks a merge request as long as the rule is violated. Many SQL linters allow a targeted exception marker right in the code, typically a comment, with which a developer deliberately justifies why SELECT * is intentional at that spot, instead of disabling the rule wholesale.

For ORM-based codebases it's also worth checking whether the ORM loads all columns by default, so-called eager loading of every field, or whether it supports projection-based loading, where only explicitly requested fields flow into the generated query. Many modern ORMs offer that option, but it's frequently not the default and has to be actively configured.

In code review itself, a simple rule of thumb helps: any new or changed query that lands in repeatedly executed application code should carry an explicit column list, unless it's demonstrably one of the legitimate exception cases named above. That rule can be written into a team style guide and enforced consistently in review instead of being re-litigated case by case.

-- Deliberate, documented exception instead of a blanket rule violation
-- lint-disable-next-line select-star -- backup routine, must copy every
-- column unchanged, including future additions
SELECT * FROM archive_source;

8. Impact on query plan caching and statistics

Database systems cache frequently executed query plans to avoid repeating optimization work. With SELECT *, the resulting plan is directly tied to the current schema: if the column count changes, the cached plan has to be discarded and recomputed, which can briefly cause elevated load for heavily used queries right after every schema change.

With an explicit column list, by contrast, the plan stays stable as long as the referenced columns themselves don't change, regardless of whether new columns get added elsewhere in the table. That reduces the number of moments where a schema deployment triggers unexpected plan recomputation and, with it, brief latency spikes.

This also matters for statistics maintenance: an optimizer that knows a query only needs three specific columns can more precisely decide which columns need detailed histograms. SELECT * provides no such information and forces the optimizer to conservatively assume a broader access pattern.

9. A migration path for existing code with many SELECT * spots

In grown codebases with hundreds of SELECT * occurrences, an abrupt full rewrite isn't worth it, a prioritized approach is. First, queries that sit in high-load paths or access tables with large, rarely needed columns get identified, because that's where the performance gain is largest. These get rewritten first and documented with a before-and-after comparison of the transferred data volume.

For the remaining backlog, a simple technical trick helps: the linter gets activated only for new and changed code first, not retroactively for the entire codebase. That avoids blocking effort for unchanged legacy queries, while every future change to a query automatically brings the requirement for an explicit column list, and the backlog shrinks organically.

It's important to back this transition with adequate test coverage, especially where application code currently depends implicitly on a particular column order or on extra, unused fields. An automated comparison of the response structure before and after the switch prevents the migration itself from introducing new, subtle bugs.

10. Summary

11. FAQ: Avoiding SELECT *: Key Takeaways