Recipe 3.1. 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, 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    | +---------------------+---------+---------+---------+---------+---------+ | 2006-05-11 10:15:08 | saturn  | barb    | mars    | tricia  |   58274 | | 2006-05-12 12:48:13 | mars    | tricia  | venus   | gene    |  194925 | | 2006-05-12 15:02:49 | mars    | phil    | saturn  | phil    |    1048 | | 2006-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 t, srcuser, srchost, size FROM mail; +---------------------+---------+---------+---------+ | t                   | srcuser | srchost | size    | +---------------------+---------+---------+---------+ | 2006-05-11 10:15:08 | barb    | saturn  |   58274 | | 2006-05-12 12:48:13 | tricia  | mars    |  194925 | | 2006-05-12 15:02:49 | phil    | mars    |    1048 | | 2006-05-13 13:59:18 | barb    | saturn  |     271 | ... 

The preceding examples use the mysql program to illustrate the differences between using * versus a list of names to specify output columns when issuing SELECT statements. These differences also can be significant when issuing statements from within programs that you write yourself, depending on how you fetch result set rows. If you select output columns using *, the server returns them using the order in which they are listed in the table definitionan order that may change if you change the definition with ALTER TABLE. If you fetch rows into an array that is indexed by column number, this indeterminate output column order makes it impossible to know which column each array element corresponds to. By naming output columns explicitly, you can fetch rows into an array with confidence that the columns will appear in the array in the same order that you named them in the statement.

Alternatively, your API may allow you to fetch rows into a structure containing elements that are accessed by name. For example, in Perl or Ruby, you can use a hash; in PHP, you can use an associative array or an object. If you use this approach, you can issue a SELECT * query and then use column names to access structure members. In this case, there is effectively no difference between selecting columns with * or by naming them explicitly: being able to access values by name within your program makes their order within result set rows irrelevant. This fact makes it tempting to take the easy way out by using SELECT * for all your queries. Nevertheless, even if you're not actually going to use every column, it's more efficient to name specifically only the columns you want so that the server doesn't send you information that you're just going to ignore. (An example that explains in more detail why you might want to avoid retrieving certain columns is given in Section 9.8, in the section "Selecting All Except Certain Columns.")




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net