Choosing Columns and Avoiding SELECT Star

Learning outcomes

A query’s select list is an interface. Applications deserialize it, reports label it, exports preserve it, and people reason from it. SELECT * delegates that interface to the current source schema.

What SELECT star means

SELECT *FROM customer;

The star is expanded to the columns available from the source. For one table it appears convenient, especially during exploration. But it does not express which columns the consumer actually needs.

Use purpose to choose columns

Start from the consumer’s question, not the table definition. A customer-picker interface may need an identifier and label. A billing export may need legal and financial fields. An operational diagnostic may temporarily need more.

-- Customer picker.SELECT    c.customer_id,    c.full_nameFROM customer AS c;-- Product catalogue card.SELECT    p.sku,    p.product_name,    p.unit_priceFROM product AS p;

Schema evolution can change star output

Suppose an application expects four customer columns. Later, the schema adds an internal risk flag or an encrypted contact field. A star query may return the new column without any query change.

CREATE TABLE customer_demo (    customer_id INTEGER PRIMARY KEY,    full_name   TEXT NOT NULL,    city        TEXT);INSERT INTO customer_demo VALUES (1, 'Nadia Rahimi', 'Tehran');SELECT * FROM customer_demo;ALTER TABLE customer_demoADD COLUMN internal_note TEXT;SELECT * FROM customer_demo;

The second query has a different shape. Explicit projections remain stable until the query author intentionally edits them.

Column order is part of many interfaces

SQL consumers often access fields by name, but CSV exports, positional mappings, legacy drivers, and tests may depend on order. Explicit projection makes the order visible.

-- Human-oriented order.SELECT    p.product_name,    p.sku,    p.unit_priceFROM product AS p;-- Machine contract order.SELECT    p.product_id,    p.sku,    p.product_name,    p.category,    p.unit_priceFROM product AS p;

Neither order is universally correct. The correct order is the documented order for that result contract.

Broad projections waste work

Returning unnecessary columns can increase data transfer, decoding, memory use, and exposure. Large text or binary values make the difference obvious, but the discipline matters even when the current tables are small.

The query optimizer may avoid some physical work depending on indexes and storage, but an explicit narrow projection gives the database the opportunity to read and transmit less.

Star becomes more dangerous with joins

When multiple tables are involved, SELECT * can return duplicate column names, repeated identifiers, and fields the consumer cannot distinguish.

-- Hard to consume and review.SELECT *FROM sale AS sJOIN customer AS c  ON c.customer_id = s.customer_id;

SELECT    s.sale_id       AS sale_id,    s.sold_at       AS sold_at,    c.customer_id   AS customer_id,    c.full_name     AS customer_name,    s.quantity      AS quantityFROM sale AS sJOIN customer AS c  ON c.customer_id = s.customer_id;

Joins are taught in Chapter 7; the important point here is that explicit output names prevent ambiguity.

When star is acceptable

The rule is not “star is syntactically wrong.” The rule is “do not let source-schema accident define a durable interface.”

Lab: refactor an unstable query

Start with this broad query:

SELECT *FROM product AS p;

Assume the consumer needs a product catalogue with a stable public identifier, label, category, and price. Refactor it:

SELECT    p.sku          AS product_code,    p.product_name AS product_name,    p.category     AS category,    p.unit_price   AS unit_priceFROM product AS p;

Now add a new internal column and verify that the explicit result shape does not change:

ALTER TABLE productADD COLUMN supplier_cost REAL;SELECT    p.sku          AS product_code,    p.product_name AS product_name,    p.category     AS category,    p.unit_price   AS unit_priceFROM product AS p;

Projection review checklist

Common mistakes

Using star to save typing

Typing is a one-time cost; unstable interfaces create recurring debugging and migration costs.

Assuming ORMs always protect the query

Generated SQL may still retrieve more than the projection requires. Inspect emitted queries and choose DTO or projection types deliberately.

Listing every column without purpose

An explicit list that blindly copies the entire table is more stable than star but may still expose and transfer unnecessary data.

Changing aliases casually

Output labels are part of a contract for many consumers. Rename them intentionally.

Summary and references

• * delegates result shape to the source schema.
• Explicit projections document purpose, order, and output names.
• Narrow results reduce accidental exposure and may reduce work.
• Star is useful for temporary exploration, not as a default production contract.
• Join queries make explicit projection even more important.

References

• SQLite: Result-column processing
• PostgreSQL: Select Lists
• Microsoft Learn: SELECT clause