9.2. Specifying Which Columns to Retrieve

To indicate what values to retrieve, name them following the SELECT keyword. In the simplest case, you specify an expression or list of expressions. MySQL evaluates each expression and returns its value. Expressions may return numbers, strings, temporal values, or NULL. The following SELECT statement retrieves a value of each of those types:

 mysql> SELECT 2+2, REPEAT('x',5), DATE_ADD('2001-01-01',INTERVAL 7 DAY), 1/0; +-----+---------------+---------------------------------------+------+ | 2+2 | REPEAT('x',5) | DATE_ADD('2001-01-01',INTERVAL 7 DAY) | 1/0  | +-----+---------------+---------------------------------------+------+ |   4 | xxxxx         | 2001-01-08                            | NULL | +-----+---------------+---------------------------------------+------+ 

The first expression is a sum of numbers and returns the number 4. The second expression returns a string ('xxxxx') consisting of the character 'x' repeated five times. The third expression returns a date value. The fourth expression returns NULL because it involves a divide-by-zero condition. In general, if MySQL finds it impossible to evaluate an expression because it involves some exceptional condition, the result is NULL or an error occurs.

SELECT can retrieve the values of expressions, as just shown, but it's more commonly used to retrieve columns from tables. To select information from a table, it's necessary to identify the table by adding a FROM table_name clause following the list of columns to retrieve. The names of the columns can be seen with DESCRIBE:

 mysql> DESCRIBE City; +-------------+----------+------+-----+---------+----------------+ | Field       | Type     | Null | Key | Default | Extra          | | ID          | int(11)  | NO   | PRI | NULL    | auto_increment | | Name        | char(35) | NO   |     |         |                | | CountryCode | char(3)  | NO   |     |         |                | | District    | char(20) | NO   |     |         |                | | Population  | int(11)  | NO   |     | 0       |                | +-------------+----------+------+-----+---------+----------------+ 

To retrieve the contents of these columns, write the SELECT statement as follows:

 SELECT ID, Name, CountryCode, District, Population FROM City; 

MySQL returns a result set consisting of one row of output for each row in the table. (The term "result set" refers to the set of rows resulting from a SELECT statement.) If the table is empty, the result will be empty, too. An empty result set is perfectly legal. A syntactically valid SELECT that returns no rows is not considered erroneous.

For a SELECT operation that retrieves every column from a table, the shortcut * can be used to specify the output columns. The * stands for "all columns in the table," so for the City table, the following statements are equivalent:

 SELECT ID, Name, CountryCode, District, Population FROM City; SELECT * FROM City; 

The * shorthand notation is clearly more convenient to type than a list of column names. However, you should understand when it is useful and when it isn't:

• If you want to retrieve all columns from a table and you don't care about the order in which they appear from left to right, * is appropriate. If you want to ensure that the columns appear left to right in a particular order, * cannot be used because it gives you no control over the order in which columns will appear. You should name the columns explicitly in the order you want to see them.

• If you don't want to retrieve all the columns from the table, you cannot use *. Instead, name the columns in the order they should appear.

You should not issue a SELECT * query to find out the current left-to-right display order for the columns in a table and then assume that they will always be displayed in that same order for future queries. The left-to-right column order produced by SELECT * retrievals depends implicitly on the internal structure of the table, which is determined by the order of the columns in the table definition. However, the table's internal structure can be changed with ALTER TABLE, so a SELECT * statement might return different results before and after an ALTER TABLE statement.

9.2.1. Renaming Retrieved Columns

Output column names, by default, are the same as the column or expression selected. To rename a column, provide an alias following the column in the output list:

 mysql> SELECT 1 AS One, 4*3 'Four Times Three'; +-----+------------------+ | One | Four Times Three | +-----+------------------+ |   1 |               12 | +-----+------------------+ 

Columns aliases are used as follows:

• The keyword AS is optional.

• An alias may be quoted. If it consists of multiple words, it must be quoted.

• You can refer to a column alias elsewhere in the query, in the GROUP BY, HAVING, or ORDER BY clause. However, you cannot refer to aliases in the WHERE clause.

9.2.2. Identifying the Database Containing a Table

When you name a table in a SELECT statement, it's normally assumed to be a table in the default database. (This is true for other statements as well.) For example, if world is the default database, the following statement selects rows from the Country table in the world database:

 SELECT * FROM Country; 

If there's no default database, the statement results in an error because MySQL cannot tell where to find the table:

 mysql> SELECT * FROM Country; ERROR 1046 (3D000): No database selected 

To specify a database explicitly in the SELECT statement itself, qualify the table name. That is, precede the table name with the database name and a period:

 SELECT * FROM world.Country; 

The database name acts as a qualifier for the table name. It provides to the server a context for locating the table. Qualified table names are useful under several circumstances:

• When there's no default database. In this case, a qualifier is necessary for accessing the table.

• When you want to select information from a table that's located somewhere other than the default database. In this situation, it's possible to issue a USE statement to select the other database as the default, a SELECT that uses the unqualified table name, and then another USE to select the original database as the default. However, qualifying the table name in the SELECT allows the two USE statements to be avoided.

• When you aren't sure what the default database is. If the default isn't the database in which the table is located, the qualifier enables the server to locate the table. If the default happens to be the same as the named database, the qualifier is unnecessary, but harmless.