Retrieving Data with SELECT and DISTINCT

Start with columns, not the star#

SELECT name, city FROM customers;

SELECT * is fine in the CLI while you explore. In application code it's a trap: add a large column later and every query gets heavier, and your code may break when column order changes.

Aliases and expressions#

SELECT name,
       price,
       price * 1.18 AS price_with_tax
FROM products;

AS creates a name for the output column. Expressions work anywhere a value does. If you alias with spaces, quote with backticks: AS `Price With Tax`.

DISTINCT#

SELECT DISTINCT city FROM customers;
SELECT DISTINCT city, status FROM customers c JOIN orders o ON o.customer_id = c.id;

DISTINCT applies to the whole row of selected columns, not just the first one. DISTINCT city, status returns unique pairs. MySQL typically implements it by building a temporary structure to track seen rows, so it costs real work on big results.

Notice that NULL shows up as one value in the DISTINCT output. For grouping purposes, MySQL treats all NULLs as the same.

The logical order of evaluation#

You write SELECT first, but the server does not run it first:

FROM / JOIN   ▶ build the row set
WHERE         ▶ filter rows
GROUP BY      ▶ form groups
HAVING        ▶ filter groups
SELECT        ▶ compute columns and aliases
DISTINCT      ▶ remove duplicates
ORDER BY      ▶ sort
LIMIT         ▶ cut

This explains the classic error: you can't use a SELECT alias in WHERE, because WHERE ran before the alias existed.

SELECT price * 1.18 AS gross FROM products WHERE gross > 100;   -- error
SELECT price * 1.18 AS gross FROM products WHERE price * 1.18 > 100;  -- works

ORDER BY and HAVING can see aliases, because MySQL allows it as an extension, but WHERE can't.

NULL in expressions#

Any arithmetic with NULL gives NULL. price + NULL is NULL. Comparing with = to NULL gives NULL, which WHERE treats as false. We'll deal with that in the next lesson.

Common mistakes#

• Using DISTINCT to hide a bad join that's creating duplicate rows. Fix the join instead.
• Putting aliases in WHERE.
• Assuming DISTINCT applies to just the first column listed.

Practice#

List every unique category in products. Then list product name with a computed column stock_value (price times stock). Finally, write a query that returns each distinct (city, status) pair for customers who have orders, and explain why Dan Brooks doesn't appear.