Query Primer - The select Clause

Even though the select clause is the first clause of a selectstatement, it is one of the last clauses to be evaluated by the database server. The reason for this is that before you can determine what to include in the final result set, you need to know all of the possible columns that could be included in the final result set. In order to fully understand the role of theselectclause, therefore, you will need to understand a bit about thefromclause. Here’s a query to get started:

  mysql> SELECT *
      -> FROM department;

  +---------+----------------+
  | dept_id | name           |
  +---------+----------------+
  |       1 | Operations     |
  |       2 | Loans          |
  |       3 | Administration |
  +---------+----------------+
  3 rows in set (0.04 sec)

In this query, thefromclause lists a single table (department), and theselectclause indicates that all columns (designated by “*”) in thedepartmenttable should be included in the result set. This query could be described in English as follows:

Show me all the columns in thedepartment table.

In addition to specifying all of the columns via the asterisk character, you can explicitly name the columns you are interested in, such as:

  mysql> SELECT dept_id, name
      -> FROM department;

  +---------+----------------+
  | dept_id | name           |
  +---------+----------------+
  |       1 | Operations     |
  |       2 | Loans          |
  |       3 | Administration |
  +---------+----------------+
  3 rows in set (0.01 sec)

The results are identical to the first query, since all of the columns in thedepartmenttable (dept_idandname) are named in theselect clause. You can choose to include only a subset of the columns in thedepartmenttable as well:

  mysql> SELECT name
      -> FROM department;

  +----------------+
  | name           |
  +----------------+
  | Operations     |
  | Loans          |
  | Administration |
  +----------------+
  3 rows in set (0.00 sec)

The job of theselectclause, therefore, is the following:

Theselectclause determines which of all possible columns should be included in the query’s result set.

If you were limited to including only columns from the table or tables named in thefromclause, things would be rather dull. However, you can spice things up by including in yourselectclause such things as:

• Literals, such as numbers or strings
  
• Expressions, such astransaction.amount *
  -1 
  
• Built-in function calls, such asROUND(transaction.amount, 2)

The next query demonstrates the use of a table column, a literal, an expression, and a built-in function call in a single query against theemployeetable:

  mysql> SELECT emp_id,
      ->  'ACTIVE',
      ->  emp_id * 3.14159,
      ->  UPPER(lname)
      -> FROM employee;

emp_id  ACTIVE  emp_id * 3.14159  UPPER(lname)
	1  ACTIVE	3.14159 SMITH
	2 ACTIVE	6.28318 BARKER
	3 ACTIVE	9.42477  TYLER
	4 ACTIVE	12.56636 HAWTHORNE
	5 ACTIVE	15.70795  GOODING
	6 ACTIVE	18.84954FLEMING
	7  ACTIVE	21.99113  TUCKER
	8ACTIVE	25.13272 PARKER
	9 ACTIVE	28.27431 GROSSMAN
	10 ACTIVE	31.41590  ROBERTS
	11 ACTIVE	34.55749 ZIEGLER
	12  ACTIVE	37.69908  JAMESON
	13  ACTIVE	40.84067  BLAKE
	14 ACTIVE	43.98226 MASON
	15 ACTIVE	47.12385 PORTMAN
	16 ACTIVE	50.26544MARKHAM
	17 ACTIVE	53.40703 FOWLER
	18  ACTIVE	56.54862 TULMAN

18 rows in set (0.05 sec)

Expressions and built-in functions will be covered in detail later, but I wanted to give you a feel for what kinds of things can be included in theselectclause. If you only need to execute a built-in function or evaluate a simple expression, you can skip thefromclause entirely. Here’s an example:

     mysql> SELECT VERSION(),
        ->   USER(),
        ->   DATABASE();

VERSION()  USER()  DATABASE()

4.1.11-nt  lrngsql@localhost  bank

1 row in set (0.02 sec)

Since this query simply calls three built-in functions and doesn’t retrieve data from any tables, there is no need for afromclause.

Next: Column Aliases >>
 

More Database Articles Articles
More By O'Reilly Media