Specifying Which Columns to Display

3.2.1 Problem

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

3.2.2 Solution

Use * as a shortcut that selects all columns. Or name the columns you want to see explicitly.

3.2.3 Discussion

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

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

Alternatively, you can list the columns explicitly:

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

It's certainly easier to use * than to write out a list of column names. However, with *, there is no guarantee about the order in which columns will be returned. (The server returns them in the order they are listed in the table definition, but this may change if you change the definition. See Chapter 8.) Thus, one advantage of naming the columns explicitly is that you can place them in whatever order you want. Suppose you want hostnames to appear before usernames, rather than after. To accomplish this, name the columns as follows:

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

Another advantage of naming the columns compared to using * is that you can name just those columns you want to see and omit those in which you have no interest:

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