8. SQL Statements : SELECT (interactive) : Select Statement Clauses : SELECT Clause
 
Share this page                  
SELECT Clause
The SELECT clause specifies which values are to be returned. To display all the columns of a table, use the asterisk wildcard character (*). For example, the following query displays all rows and columns from the employees table:
SELECT * FROM employees;
To select specific columns, specify the column names. For example, the following query displays all rows, but only two columns from the employees table:
SELECT ename, enumber FROM employees;
To specify the table from which the column is to be selected, use the table.column_name syntax. For example:
SELECT managers.name, employees.name
        FROM managers, employees...
In the preceding example, both source tables contain a column called name. The column names are preceded by the name of the source table; the first column of the result table contains the values from the name column of the managers table, and the second column contains the values from the name column of the employees table. If a column name is used in more than one of the source tables, qualify the column name with the table to which it belongs, or with a correlation name. For details, see FROM Clause (see FROM Clause).
The number of rows in the result table can be limited using the FIRST clause. RowCount is a positive integer value that indicates the maximum rows in the result table. The query is effectively evaluated without concern for the FIRST clause, but only the first ā€œnā€ rows (as defined by rowCount) are returned. This clause cannot be used in a WHERE clause subselect and it can only be used in the first of a series of UNIONed selects. However, it can be used in the CREATE TABLE...AS SELECT and INSERT INTO...SELECT statements.
To eliminate duplicate rows from the result table, specify the keyword DISTINCT. To preserve duplicate rows, specify the keyword ALL. By default, duplicate rows are preserved.
For example, the following table contains order information; the partno column contains duplicate values, because different customers have placed orders for the same part:
partno
customerno
qty
unit_price
123-45
101
10
10.00
123-45
202
100
10.00
543-21
987
2
99.99
543-21
654
33
99.99
987-65
321
20
29.99
The following query displays the part numbers for which there are orders on file:
SELECT DISTINCT partno FROM orders
The result table looks like this:
Partno
123-45
543-21
987-65
A constant value can be included in the result table. For example:
SELECT 'Name:', ename, CURRENT_DATE,
        IFNULL(edept,'Unassigned')
        FROM employees;
The preceding query selects all rows from the employees table; the result table is composed of the string constant 'Name:', the name of the employee, today's date, and the employee's department, or if there is no department assigned, the string constant 'Unassigned'.
The result table looks like this (depending, of course, on the data in the employees table):
COL1
Ename
COL3
COL4
Name:
Mike Sannicandro
1998-08-08
Shipping
Name:
Dave Murtagh
1998-08-08
Purchasing
Name:
Benny Barth
1998-08-08
Unassigned
Name:
Dean Reilly
1998-08-08
Lumber
Name:
Al Obidinski
1998-08-08
Unassigned
The SELECT clause can be used to obtain values calculated from the contents of a table. For example, the following query calculates the weekly salary of each employee based on their annual salary:
SELECT ename, annual_salary/52 FROM employees;
Aggregate functions can be used to calculate values based on the contents of a column. For example, the following query returns the highest, lowest, and average salary from the employees table:
SELECT MAX(salary), MIN(salary), AVG(salary)
        FROM employees;
These values are based on the amounts stored in the salary column.
To specify a name for a column in the result table, use the AS result_column clause. In the following example, the name, weekly_salary, is assigned to the second result column:
SELECT ename, annual_salary/52 AS weekly_salary
        FROM employees;
If a result column name is omitted for columns that are not drawn directly from a table (for example, calculated values or constants), the result columns are assigned the default name COLn, where n is the column number; result columns are numbered from left to right. Column names cannot be assigned in SELECT clauses that use the asterisk wildcard (*) to select all the columns in a table.