Previous Section  < Day Day Up >  Next Section

5.1 Identifying What Values to Display

To indicate what values to display, 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 returns 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 and cannot be evaluated. In general, if MySQL finds it impossible to evaluate an expression because it involves some exceptional condition, the result is NULL.

SELECT can serve to evaluate expressions, but it's more commonly used to retrieve values from a table. To retrieve information from a table, it's necessary to identify the table by adding a FROM table_name clause following the list of values to display. Suppose that you have a table named personnel that contains three columns named id, name, and salary, in that order:







mysql> DESCRIBE personnel;

+--------+---------------+------+-----+---------+-------+

| Field  | Type          | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| id     | int(11)       |      | PRI | 0       |       |

| name   | char(20)      | YES  |     | NULL    |       |

| salary | decimal(10,2) | YES  |     | NULL    |       |

+--------+---------------+------+-----+---------+-------+

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







SELECT id, name, salary FROM personnel;

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 * may be used to specify the output columns. The * stands for "all columns in the table," so for the sample table, the following statements are equivalent:







SELECT id, name, salary FROM personnel;

SELECT * FROM personnel;

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.

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

As an example of when * is inapplicable, assume that you want to retrieve all columns from personnel, but in the order salary, id, and name. In this case, instead of using *, you should name the columns explicitly in the order you want them displayed:







SELECT salary, id, name FROM personnel;

Note that you cannot issue a SELECT * query to find out the left-to-right display order for the columns in a table and then assume that for future queries they will always be displayed in the same order. That strategy depends implicitly on the internal structure of the table because the actual left-to-right column order used for SELECT * retrievals 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 the strategy is unsafe. A SELECT * statement might return different results before and after an ALTER TABLE statement.

5.1.1 Using Aliases to Name Output Columns

Output column names, by default, are the same as the column or expression selected. To give a specific name to 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 can be 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. You cannot refer to it in the WHERE clause.

5.1.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: No Database Selected

To specify a database explicitly, 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 is simpler because it 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 allows the server to locate the table. If the default happens to be the same as the named database, the qualifier is unnecessary, but harmless.

5.1.3 Retrieving Records from Multiple Tables

It's possible to retrieve records from more than one table in a single query. One way to do this is by means of a join, which selects records from multiple tables simultaneously. Joins are covered in Chapter 8, "Joins." Another way is to select records from one table after the other with multiple SELECT statements and concatenate the results using the UNION operator. UNION is covered in section 5.7, "Concatenating SELECT Results with UNION."

    Previous Section  < Day Day Up >  Next Section