MySQL – SELECT | Love Me Some Coding

SELECT is the statement you will write more than all the others put together. It reads rows out of one or more tables and hands them back as a result set. This lesson covers choosing columns, naming them, removing duplicates, computing values, and the one piece of theory that explains most of the confusing errors beginners hit: the order MySQL actually evaluates a query in, which is not the order you type it.

Every query here runs against the pizza database from the demo application — 14 products, 42 price rows, 18 orders. Small enough that you can check the answers by eye.

Picking columns

Name the columns you want, separated by commas:

SELECT name, type
FROM   product
ORDER  BY id
LIMIT  5;

+---------------------+-------+
| name                | type  |
+---------------------+-------+
| Pepperoni Pizza     | PIZZA |
| Cheese Pizza        | PIZZA |
| Supreme Pizza       | PIZZA |
| Meat Lovers Pizza   | PIZZA |
| Veggie Lovers Pizza | PIZZA |
+---------------------+-------+

Why not `SELECT *`

SELECT * returns every column. It is genuinely useful when you are exploring a table you do not know, and it is a bad habit everywhere else:

It moves data you are not going to use. product has a 500-character description; a menu list that only needs the name pays for it on every row.
It stops your query being covered by an index, which is one of the larger performance levers available — see the indexes lesson.
It breaks silently when the schema changes. Add a column and every SELECT * quietly starts returning it; your application maps results by position and now reads the wrong field.

Explore with the star, ship with a column list.

Aliases

AS renames a column in the output. The alias is what your application sees, so it is worth using whenever the column name is not the name you want:

SELECT name AS pizza, description AS blurb
FROM   product
WHERE  type = 'PIZZA'
ORDER  BY display_order
LIMIT  3;

+-----------------+-----------------------------------------------------------+
| pizza           | blurb                                                     |
+-----------------+-----------------------------------------------------------+
| Pepperoni Pizza | Classic pepperoni over mozzarella and our signature sauce |
| Cheese Pizza    | Simple, generous mozzarella on a hand-stretched base      |
| Supreme Pizza   | Pepperoni, sausage, peppers, onions, mushrooms and olives |
+-----------------+-----------------------------------------------------------+

The AS is optional — name pizza works — but leaving it out makes a missing comma look like an alias instead of an error, so write it.

Tables take aliases too, and once a query joins anything you will want them: FROM product p lets you write p.name. That is covered in the joins lesson.

DISTINCT

DISTINCT removes duplicate rows from the result:

SELECT DISTINCT type FROM product;

+-------+
| type  |
+-------+
| DRINK |
| PIZZA |
+-------+

Two things worth knowing. DISTINCT applies to the whole row, not to the column it appears next to — SELECT DISTINCT a, b gives distinct pairs, not distinct a. And it is not free: MySQL has to sort or hash the result to find the duplicates.

Inside COUNT it counts distinct values:

SELECT COUNT(*) AS rows_, COUNT(DISTINCT type) AS types
FROM   product;

+-------+-------+
| rows_ | types |
+-------+-------+
|    14 |     2 |
+-------+-------+

Expressions

The select list is not limited to columns. Arithmetic, function calls and literals all work:

SELECT p.name, ps.price, ROUND(ps.price * 1.0725, 2) AS with_tax
FROM   product_size ps
JOIN   product p ON p.id = ps.product_id
WHERE  ps.size = 'LARGE'
ORDER  BY ps.price DESC
LIMIT  4;

+-----------------------+-------+----------+
| name                  | price | with_tax |
+-----------------------+-------+----------+
| Meat Lovers Pizza     | 20.99 |    22.51 |
| Supreme Pizza         | 19.99 |    21.44 |
| BBQ Chicken Pizza     | 19.99 |    21.44 |
| Buffalo Chicken Pizza | 19.99 |    21.44 |
+-----------------------+-------+----------+

Note the ROUND. price is DECIMAL(10,2), and multiplying a DECIMAL gives you every digit of the exact answer — 22.511775 without it. Money arithmetic is covered in data types.

The order MySQL actually evaluates a query in

You write a query in this order:

SELECT  ->  FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  ORDER BY  ->  LIMIT

MySQL evaluates it in a different one:

FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  DISTINCT  ->  ORDER BY  ->  LIMIT

SELECT runs second to last. That single fact explains a family of errors that otherwise look arbitrary — a column alias does not exist yet in WHERE, because WHERE ran before the select list was evaluated:

-- ERROR 1054 (42S22): Unknown column 'with_tax' in 'where clause'
SELECT price * 1.0725 AS with_tax
FROM   product_size
WHERE  with_tax > 20;

The fix is to repeat the expression in WHERE, or wrap the query in a derived table.

By the time ORDER BY runs, though, the select list has been evaluated — so the same alias works there:

SELECT p.name, ps.price * 1.0725 AS with_tax
FROM   product_size ps
JOIN   product p ON p.id = ps.product_id
WHERE  ps.size = 'LARGE'
ORDER  BY with_tax DESC
LIMIT  3;

+-------------------+-----------+
| name              | with_tax  |
+-------------------+-----------+
| Meat Lovers Pizza | 22.511775 |
| Supreme Pizza     | 21.439275 |
| BBQ Chicken Pizza | 21.439275 |
+-------------------+-----------+

HAVING can use an alias for the same reason; WHERE and GROUP BY cannot.

What to remember

Name your columns. Keep SELECT * for exploring.
AS is optional and worth writing anyway — a missing comma should look like an error, not an alias.
DISTINCT applies to the whole row, and it costs a sort.
SELECT is evaluated second to last. Aliases work in ORDER BY and HAVING, and not in WHERE.

Next: WHERE, which is where most of the work of a real query happens.

MySQL – SELECT