MySQL Cookbook, 2nd Edition

Specifying Which Columns to Select

Problem

You want to display some or all of the columns from a table.

Solution

Use SELECT * as a shortcut that selects all columns. However, with SELECT *, you always get all columns, and you can’t assume anything about the order in which they’ll appear. To retrieve only some of the columns, or require that they appear in a certain order, either name the columns explicitly in the desired display order or retrieve them into a data structure that makes their order irrelevant.

Discussion

To indicate what kind of information you want to select from a table, name a column or a list of columns and the table to use. The easiest way to display columns from a table is to use SELECT * FROM tbl_name. The * specifier is a shortcut for naming all the columns in a table:

mysql>SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
...

Using * is easy, but you cannot specify the column display order. An advantage of naming columns explicitly is that you can display them in whatever order you want. Suppose that you want hostnames to appear before usernames, ...