SQL Query Fundamentals Questions

SELECT * pulls back every column, including ones the query doesn't need, ones that might be large (long text/blob columns), and ones that could change shape entirely if the table's schema evolves, none of which serve a production dashboard or ETL (extract, transform, load) job well.

Structured elaboration

Three concrete reasons to avoid SELECT * outside of quick ad hoc exploration:

1. Performance: pulling unnecessary columns (especially wide or large ones) wastes I/O and network transfer, particularly costly at scale or across a network boundary (an API layer, a BI (business intelligence) tool's live connection).
2. Schema drift: if a column is later added to the table, SELECT * silently starts returning it too, potentially breaking a downstream consumer that assumes a fixed column count or order, or bloating a report that never needed the new column in the first place. If a column is renamed or dropped, SELECT * consumers may break in ways an explicit column list would have caught earlier, at the point of the schema change rather than downstream.
3. Readability: an explicit column list documents, right in the query, exactly what data is being used; SELECT * forces a reader to go look up the table's current schema to understand what's actually being returned.

Worked example

A dashboard query using SELECT * FROM orders continues to "work" (no error) after a new internal_notes text column is added to the orders table, but now silently transmits and potentially displays that column's contents, information the dashboard's author never intended to expose and the report's consumers never asked for.

Trade-offs and pitfalls

The better alternative is always naming the specific columns needed, even when that's more typing up front; some teams additionally rely on a view (a stored, named SELECT with an explicit column list) as a stable interface layer specifically so downstream consumers are insulated from the base table's schema evolving underneath them.

Joining a customers table directly to order_items and summing produces an inflated total whenever a customer has more than one order, because each order's line items get re-multiplied by however many order rows are being joined through; pre-aggregating order_items to order-level totals first, then joining up, avoids the inflation.

Structured elaboration

-- WRONG shape (works fine for a single order, but is a latent bug)
SELECT c.id, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.id;

-- SAFE shape: pre-aggregate order_items to order totals, then join up
WITH order_totals AS (
  SELECT order_id, SUM(qty * unit_price) AS order_revenue
  FROM order_items GROUP BY order_id
)
SELECT c.id, COUNT(o.order_id) AS order_count, SUM(ot.order_revenue) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals ot ON ot.order_id = o.order_id
GROUP BY c.id;

The bug in the first shape isn't in the SUM itself, it's that the join produces one row per (order, order_item) pair, and a customer-level COUNT or a mis-scoped aggregate over that joined result silently reflects the item-level grain, not the order-level or customer-level grain you're mentally picturing.

Worked example

A customer with one order containing two line items ($100 and $30, order total $130): a naive COUNT(o.order_id) after joining through order_items returns 2 (one count per line item), not 1 (the actual number of orders), even though SUM(qtyunit_price) happens to still total correctly to $130 in this single-order case. Pre-aggregating order_items to order totals first, then joining, correctly reports order_count = 1 and revenue = $130. The risk compounds with more orders: any customer-level COUNT taken directly off the item-joined result will always be inflated by however many items each order has, while SUM(qtyunit_price) itself stays numerically correct only because multiplication and summation happen to commute here, a coincidence that doesn't extend to non-additive metrics like COUNT or AVG taken at the wrong grain.

Trade-offs and pitfalls

The general rule: whenever a query joins through a one-to-many (or many-to-many) relationship, ask explicitly "what does one row of this joined result actually represent" before writing any aggregate on top of it, and pre-aggregate to the coarser grain BEFORE joining up to an even coarser one, rather than aggregating across every level in a single pass.

A distinct-count-per-period metric like Daily Active Users needs COUNT(DISTINCT user_id) specifically, not a plain COUNT, because a plain COUNT would count every event, over-counting any user who was active more than once in the same period.

Structured elaboration

SELECT DATE(occurred_at) AS day, COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day;

COUNT(DISTINCT user_id) deduplicates within each day's group, so a user who logged three events on the same day still contributes exactly 1 to that day's count, correctly answering "how many distinct users were active", not "how many events happened". The GROUP BY-based alternative pushes the deduplication into an inner query instead of an aggregate function: SELECT day, COUNT(*) AS dau FROM (SELECT DISTINCT DATE(occurred_at) AS day, user_id FROM events) t GROUP BY day. The inner SELECT DISTINCT collapses each user down to at most one row per day first, so by the time the outer COUNT(*) runs there's nothing left to deduplicate, it's just counting already-unique rows.

Worked example

Given events for user 1 twice and user 2 once, all on 2025-01-01, and user 3 once on 2025-01-02: the query correctly returns DAU of 2 for 2025-01-01 (users 1 and 2, with user 1's repeat event not double-counted) and 1 for 2025-01-02 (user 3).

Trade-offs and pitfalls

Both forms are logically equivalent and, on the worked example below, return the identical numbers; which one an optimizer executes faster can differ by engine and data distribution, so it's worth trying both against real data rather than assuming one is always better.

COUNT(DISTINCT ...) is measurably more expensive than a plain COUNT at large scale, since the engine typically needs to sort or hash the values to deduplicate them; for very high-cardinality, very large-scale distinct counting (billions of events per day), an approximate distinct-count function (HyperLogLog-based, such as Postgres's hll extension or BigQuery's APPROX_COUNT_DISTINCT) trades a small, bounded error for dramatically better performance, a trade-off worth naming even though it's outside this topic's core scope.

A TIMESTAMP column includes a time-of-day component, so a date-only BETWEEN upper bound like '2024-03-31' is treated as midnight at the start of that day, silently excluding every row later that same day; a half-open range using the next day's start avoids the gap.

Structured elaboration

-- Misses late-March-31 rows
SELECT order_id FROM orders WHERE created_at BETWEEN '2024-03-01' AND '2024-03-31';
-- Correct half-open range
SELECT order_id FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

BETWEEN '2024-03-01' AND '2024-03-31' is shorthand for >= '2024-03-01' AND <= '2024-03-31', and that upper bound, when compared against a TIMESTAMP, is interpreted as 2024-03-31 00:00:00. Any row timestamped later that same day (say, 2024-03-31 23:00:00) is strictly greater than midnight and gets excluded, even though it clearly belongs in "March 2024" by any reasonable interpretation.

Worked example

Given orders at 2024-03-31 23:00:00 and 2024-03-15 10:00:00: the BETWEEN version returns only the March 15 order, silently dropping the March 31 order entirely. The half-open version (< '2024-04-01') correctly returns both orders.

Trade-offs and pitfalls

The half-open-range fix generalizes to any period boundary (a single day, a month, a quarter, a year): always express the upper bound as "the start of the next period, exclusive" rather than "the last moment of this period, inclusive", since the latter requires knowing the column's precision exactly (is it seconds? milliseconds? does '2024-03-31 23:59:59' actually cover a row at '2024-03-31 23:59:59.500'?) while the former never does. This same half-open form is also what makes the query index-friendly, which the question asks for by name: created_at >= '2024-03-01' AND created_at < '2024-04-01' compares the raw created_at column directly against two literal bounds, exactly the shape a B-tree index (the default index structure in most relational engines, storing column values in sorted order) on created_at can use for a plain range scan. A common alternative fix, wrapping the column instead of the bound, e.g. WHERE DATE(created_at) BETWEEN '2024-03-01' AND '2024-03-31' or CAST(created_at AS DATE) = ..., looks similarly correct but applies a function to every row's created_at value before comparing it, which prevents a plain index on created_at from being used at all, since the engine can no longer look up sorted values directly and instead has to compute the expression for every row. Keeping the column bare on one side of a plain comparison is what index-friendly means in practice.

Joining through a one-to-many relationship (customer to orders to order_items) means a plain COUNT after the join counts item-rows, not distinct products; COUNT(DISTINCT product_id) is what actually answers "how many different products".

Structured elaboration

SELECT c.customer_id, COUNT(DISTINCT oi.product_id) AS unique_products_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id;

Once you join customers to orders to order_items, each customer can appear once per order-item row, not once per customer. A plain COUNT(*) or COUNT(oi.product_id) after this join counts rows in the joined result, which over-counts if the customer bought the same product more than once, or simply reflects total items purchased rather than distinct products. COUNT(DISTINCT product_id) deduplicates within each customer's group, correctly answering the "how many different products" question. Separately, an outer join can introduce NULL product_ids for customers with no purchases at all; COUNT(DISTINCT ...) already ignores NULLs, so this is handled for free.

Worked example

Given one customer with two orders, one order having two order_items (products 5 and 6) and the other having one order_item (product 5 again): COUNT(oi.product_id) returns 3 (three item-rows total), while COUNT(DISTINCT oi.product_id) correctly returns 2 (products 5 and 6, with the repeat of 5 not double-counted).

Now add a second customer with zero orders at all, reached only via the outer join. For that customer, oi.product_id comes back NULL. Side by side: COUNT(*) counts that NULL row anyway, wrongly showing 1 for a customer who has made no real purchases; COUNT(oi.product_id) correctly returns 0, since COUNT of a column never counts NULLs; and COUNT(DISTINCT oi.product_id) also correctly returns 0, for the same reason. So COUNT(*) is the one form that needs the outer join's NULLs actively guarded against (or avoided by counting a non-nullable column instead), while both COUNT(oi.product_id) and COUNT(DISTINCT oi.product_id) handle them safely without any extra work.

Trade-offs and pitfalls

This is a specific instance of a broader pattern: any COUNT taken after a one-to-many join needs to be checked against what grain the join actually produced, not the grain you're mentally picturing (customers, in this case) has become. COUNT(DISTINCT) is the fix here; the more general fix for other aggregate types (SUM, AVG) is pre-aggregating before joining, covered in the double-counting question.