SQL Query Fundamentals Questions

DISTINCT and GROUP BY can produce the identical result for a plain "how many unique X" question, but GROUP BY additionally lets you compute an aggregate per group in the same query, which DISTINCT alone cannot do.

Structured elaboration

-- DISTINCT (just the unique values, no aggregate)
SELECT DISTINCT user_id FROM page_views WHERE page_id = 100;
-- GROUP BY (lets you also count views per user, for instance)
SELECT user_id, COUNT(*) AS views FROM page_views WHERE page_id = 100 GROUP BY user_id;

For "how many unique users viewed page 100", both SELECT COUNT(DISTINCT user_id) and SELECT COUNT(*) FROM (SELECT user_id FROM page_views WHERE page_id=100 GROUP BY user_id) give the same number; the optimizer typically treats them equivalently. GROUP BY earns its keep once you need something per-group beyond simple deduplication.

Worked example

Given page_views(user_id, page_id, viewed_at) with user 1 viewing page 100 twice and user 2 viewing it once: SELECT DISTINCT user_id FROM page_views WHERE page_id=100 returns two rows (users 1 and 2). SELECT page_id, COUNT(DISTINCT user_id) FROM page_views GROUP BY page_id returns page 100 with 2 unique users, the same underlying count, from a query shape that could also report each user's individual view count in the same pass if needed.

Trade-offs and pitfalls

For pure deduplication with no aggregate need, DISTINCT is more direct and often reads more clearly to the next person; reach for GROUP BY once you're already computing an aggregate (COUNT, SUM, etc.) per group, rather than adding an unnecessary DISTINCT+subquery when GROUP BY would do both jobs in one pass.

A two-dimension bucket (customer AND month) is a GROUP BY on both a foreign key and a truncated date expression together, producing one row per customer per active month rather than one total per customer or one total per month.

Structured elaboration

SELECT customer_id, strftime('%Y-%m', order_date) AS month, SUM(amount) AS monthly_revenue
FROM orders
GROUP BY customer_id, month
ORDER BY customer_id, month;

This is the same GROUP BY-on-multiple-columns mechanic as any multi-dimension breakdown, just with one dimension being a plain identifier (customer_id) and the other a computed date-truncation expression (month). The output naturally has one row per customer per month in which that customer had at least one order, again omitting customer/month combinations with zero orders unless combined with the zero-fill technique (generate every customer/month combination that should appear, for example via a calendar/date-series CTE cross-joined against customers, then LEFT JOIN the aggregated revenue onto that full combination list so a zero-order month shows an explicit 0 in the result instead of simply being absent from it).

Worked example

Given customer 1 with orders of $100 (January) and $50 (January), and $30 (February): the query returns two rows for customer 1, January with $150 and February with $30, correctly separating the two months rather than combining them into one customer-level total.

Trade-offs and pitfalls

This shape (entity x time period) is the foundation of most cohort and retention-style reporting; combined with the zero-fill technique (to show customer/month pairs with zero activity) and a running total (a window function, out of this topic's scope), it becomes a full cohort revenue table.

LIKE treats % and _ as wildcards, so to match them as literal characters you need an ESCAPE clause.

Structured elaboration

SELECT email FROM users
WHERE email LIKE '%\_%' ESCAPE '\';

The backslash before the underscore tells the engine "this next character is literal, not a wildcard", because ESCAPE '' declares backslash as the escape character for this pattern. Without the escape, _ would match any single character, so LIKE '%_%' alone matches virtually every non-empty email, not just ones containing a literal underscore.

Worked example

Given emails 'a_b@x.com', 'aXb@x.com', 'a%b@x.com': the escaped pattern correctly matches only 'a_b@x.com'. Without the escape clause, all three would match (the _ wildcard matches the X and the % in the other two rows).

For the percent case, given the same three emails plus 'acb@x.com' (no special character at all): the escaped pattern LIKE '%\%%' ESCAPE '\' matches only 'a%b@x.com'. 'a_b@x.com', 'aXb@x.com', and 'acb@x.com' are all correctly excluded, since none of them contain a literal percent sign. Without the escape, an unescaped % wildcard matches zero or more of any character, so a pattern like LIKE '%%' matches every row regardless of content, the same false-positive failure mode as the unescaped underscore case above.

Trade-offs and pitfalls

The exact escape syntax differs slightly by engine (PostgreSQL and MySQL both support ESCAPE; SQL Server does too), but the concept is standard SQL. For matching a literal %, use the same technique: LIKE '%\%%' ESCAPE '\'. If a column regularly contains characters that need escaping, a regex-based match (~ in Postgres, REGEXP in MySQL) is often more readable than juggling escape characters in LIKE.

Labeling the NULL group with COALESCE is a display-layer fix; it's worth thinking through separately from whether NULLs should also be preserved for analysis, and from what happens when that labeled value later needs to join back to a real dimension table.

Structured elaboration

SELECT COALESCE(customer_segment, 'Unknown') AS segment, COUNT(*) AS n
FROM customers
GROUP BY COALESCE(customer_segment, 'Unknown');

Two considerations beyond the basic COALESCE-for-display pattern:

1. Keeping the NULL group visible for analysis: sometimes you want both the labeled dashboard view AND a way to drill into "why do these customers have no segment at all" (a data-quality question in its own right), so don't let the COALESCE'd label become the only representation of that data downstream. Concretely, run a dedicated query alongside the COALESCE one: SELECT * FROM customers WHERE customer_segment IS NULL. Against the worked-example data below (customers 2 and 4), this returns exactly those two rows in full, letting an analyst drill into who they are and why their segment is missing, something the COALESCE'd dashboard view alone can't answer since it has already collapsed both rows into a single 'Unknown' count.
2. Joining back to a segment dimension table: if 'Unknown' is joined against a real segments dimension table that has no row for the literal string 'Unknown', the join will silently drop those rows (an INNER JOIN) or produce NULLs for the dimension's other columns (a LEFT JOIN), which is a second, distinct NULL-handling decision layered on top of the first.

Worked example

Given customers(customer_id, customer_segment) with rows (1,'SMB'), (2,NULL), (3,'Enterprise'), (4,NULL): the query correctly groups the two NULL customers under 'Unknown', alongside SMB (1) and Enterprise (1), for three total groups.

Trade-offs and pitfalls

The failure mode to watch for is compounding: COALESCE handles the grouping and display, but if a later join to a dimension table isn't also NULL-aware (or Unknown-aware), the same underlying "missing segment" data problem resurfaces as a second, harder-to-spot bug at the join step.

INNER JOIN keeps only rows with a match on both sides; LEFT JOIN keeps every row from the left table, filling in NULLs for unmatched right-side columns; RIGHT JOIN is the mirror image (every right-table row kept); FULL OUTER JOIN keeps every row from both sides, matched or not.

Structured elaboration

Given customers and orders:

• INNER JOIN: only customers who have at least one order appear, one row per matching order.
• LEFT JOIN: every customer appears at least once, even those with zero orders (order columns come back NULL for them).
• RIGHT JOIN: every order appears, even one somehow pointing to a customer_id with no matching customer row (rare in practice with foreign keys enforced, but the join type still matters conceptually, and RIGHT JOIN A B is exactly equivalent to LEFT JOIN B A with sides swapped).
• FULL OUTER JOIN: every customer AND every order appear, matched where possible, NULL-padded where not; useful for reconciliation tasks (e.g., "which customers have no orders, and which orders point to no customer").

Worked example

Given customers Ann (id 1) and Bo (id 2), with only Ann having an order (order 10): INNER JOIN returns only (Ann, order 10). LEFT JOIN returns (Ann, order 10) AND (Bo, NULL), since Bo is kept even without a match.

Now add order 20, which points to customer_id 3 (no matching customer row, e.g. because the customer was deleted after placing the order, or bad data): RIGHT JOIN returns (Ann, order 10) AND (NULL, order 20), keeping order 20 even though it has no matching customer, mirroring how LEFT JOIN kept Bo despite no matching order. FULL OUTER JOIN returns all three rows: (Ann, order 10), (Bo, NULL), and (NULL, order 20), the union of everything LEFT JOIN and RIGHT JOIN would each produce separately, capturing both the customer with no order and the order with no customer in one result.

Trade-offs and pitfalls

The choice isn't stylistic: "how many customers do we have" needs a query that includes zero-order customers (LEFT JOIN, or better, no join at all if orders data isn't needed), while "what's our order fulfillment rate" naturally starts from an INNER JOIN or the orders table directly. Choosing INNER JOIN by habit when LEFT JOIN was needed is a common way to silently under-report a metric (e.g., "average orders per customer" excluding the zero-order customers entirely, inflating the average).