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.

An upsert inserts a new row when the key doesn't exist yet, and updates the existing row's other columns when it does, all as one atomic statement rather than a separate check-then-insert-or-update round trip.

Structured elaboration

-- PostgreSQL / SQLite
INSERT INTO users (id, email, name, updated_at)
VALUES (1, 'a@x.com', 'Ann Updated', now())
ON CONFLICT (id) DO UPDATE
SET name = excluded.name, updated_at = excluded.updated_at;

ON CONFLICT (id) names the constraint (here, the primary key) that would otherwise cause a duplicate-key error; DO UPDATE SET ... = excluded.column uses the special excluded pseudo-table to reference the values that were being inserted, applying them as an UPDATE instead of failing. SQL Server's equivalent is the MERGE statement, which explicitly handles matched (update) and not-matched (insert) branches in one statement, a more verbose but conceptually similar tool.

Worked example

Given an existing row (1, 'a@x.com', 'Ann', '2025-01-01') and an upsert attempt with the same id=1 but name='Ann Updated' and a new updated_at: the row is updated in place to (1, 'a@x.com', 'Ann Updated', '2025-02-01'), with no duplicate row created and no error raised for the id conflict.

Trade-offs and pitfalls

Upserts are especially valuable for idempotent data loads: if the same load job runs twice (a retry after a network failure, for instance), an upsert-based load produces the same final state either way, where a plain INSERT would either error on the second run (duplicate key) or, worse, silently succeed and create duplicate rows if there's no unique constraint to catch it.

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.

NULL means "no value recorded"; an empty string '' is an actual, present value that happens to contain zero characters. They are not the same, and IS NULL will not match an empty string (nor will = '' match a NULL).

Structured elaboration

-- Missing (NULL) values
SELECT contact_id FROM contacts WHERE phone IS NULL OR email IS NULL;
-- Empty-string values
SELECT contact_id FROM contacts WHERE phone = '' OR email = '';

These are two genuinely different queries returning two genuinely different sets of rows, because SQL treats "no value was ever recorded" (NULL) and "a value was recorded, and it's the empty string" ('') as distinct states. Using = NULL to try to catch both (or either) fails outright: = NULL is never TRUE, because NULL represents an unknown value, and asking whether an unknown value equals NULL is itself an unanswerable question; SQL returns UNKNOWN rather than TRUE or FALSE for that comparison, and a WHERE clause only keeps rows where the predicate evaluates to TRUE, so a row with a genuinely NULL phone or email is silently excluded by = NULL even though it's exactly the row being searched for.

Worked example

Given contacts(contact_id, email, phone) with rows (1, NULL, '555'), (2, 'a@x.com', ''), (3, 'b@x.com', '555'): the NULL-checking query returns only contact 1 (NULL email). The empty-string-checking query returns only contact 2 (empty phone). Neither query alone catches both "missing" representations.

Trade-offs and pitfalls

If a system genuinely uses both NULL and '' interchangeably to mean "missing" (common when data is ingested from a CSV or a form that submits blank strings instead of omitting the field), a "find missing contacts" query needs both conditions explicitly combined: WHERE phone IS NULL OR phone = '', and ideally the underlying inconsistency should be fixed at the source rather than patched at every downstream query.

UNION removes duplicate rows across the two combined result sets; UNION ALL keeps every row from both, including duplicates, and is both faster (no deduplication work) and the correct choice whenever duplicates are meaningful or you already know the two sources can't overlap.

Structured elaboration

-- UNION: deduplicates
SELECT x FROM a UNION SELECT x FROM b;
-- UNION ALL: keeps everything, including duplicates
SELECT x FROM a UNION ALL SELECT x FROM b;

Deduplication in UNION isn't free: the engine has to sort or hash the combined result to find and remove duplicates, work UNION ALL skips entirely. In an ETL (extract, transform, load) or reporting pipeline combining, say, two years of sales data that are known not to overlap (different date ranges, different source systems with no shared keys), UNION ALL is both correct and meaningfully cheaper; reaching for UNION out of habit here does unnecessary work for a deduplication guarantee nothing actually needed.

Worked example

Given table a with values (1, 2) and table b with values (2, 3): UNION returns (1, 2, 3), deduplicating the shared value 2. UNION ALL returns (1, 2, 2, 3), preserving both occurrences of 2.

Trade-offs and pitfalls

The dangerous direction is using UNION where UNION ALL was actually needed and duplicates were meaningful: combining two sample sets for a machine-learning training set with UNION, for instance, would silently drop legitimate duplicate rows (say, two different customers who happen to share identical feature values), quietly changing the resulting class balance without any error or warning. Default to UNION ALL unless you specifically need, and have thought through, deduplication.