Unit 02.00: Naming columns instead of SELECT *

Unit ID: SQL-M02-U01 - Estimated active time: 12-15 minutes Objective: explain what SELECT * costs in a query you intend to keep, and write an explicit column list.

The convenience that becomes a liability

SELECT * is perfect while you are looking around:

SELECT * FROM orders LIMIT 5;

It is a poor choice the moment the query is saved, scheduled, or pasted into a report. Three reasons, in order of how badly they bite:

1. The result shape can change without you. Someone adds a column upstream and your export gains a

field. Someone reorders columns and a positional consumer breaks.

1. You stop declaring intent. A reader cannot tell which columns actually matter to the answer.
2. You move data you do not need. On wide tables this is the difference between a fast query and a slow one.

Say what you mean

SELECT order_id, customer_id, placed_at, order_total
FROM orders
LIMIT 5;

Now the query has a contract. If order_total disappears upstream, this fails loudly - which is what you want - instead of silently producing a report with a missing column.

Worked example

Count orders by status. Both queries return the same two rows today:

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;
-- completed | 988
-- pending   |  12

The explicit version tells the reader that status is the dimension and the count is the measure. SELECT * here would not even be valid alongside the aggregate - which is a hint that being specific is the normal case and * is the exception.

Non-example

SELECT * is entirely reasonable here:

SELECT * FROM orders WHERE order_id = 501;

You are inspecting one row to see what is in it. You are not saving this. Nothing downstream depends on the shape. Use the convenience where it is genuinely convenient.

Practice

Write a query returning, for each order, only: the order id, the placed date (not the full timestamp), and the total - for completed orders only, most recent first, limited to 10 rows.

SELECT order_id,
       CAST(placed_at AS DATE) AS placed_on,
       order_total
FROM orders
WHERE status = 'completed'
ORDER BY placed_at DESC
LIMIT 10;

Takeaway

Use * to look, name columns to deliver. If the query will be run again by anyone including future you, it needs an explicit column list.

---

Mark complete