User's Guide
   PART 1. Working with Databases
     CHAPTER 4. Queries: Selecting Data from a Table       

The SELECT clause: specifying columns

The select list 

The select list commonly consists of a series of column names separated by commas, or an asterisk as shorthand to represent all columns.

More generally, the select list can include one or more expressions, separated by commas. The general syntax for the select list looks like this:

SELECT expression [, expression ]...

If any table or column name in the list does not conform to the rules for valid identifiers, you must enclose the identifier in double quotes.

The select list expressions can include * (all columns), a list of column names, character strings, column headings, and expressions including arithmetic operators. You can also include aggregate functions, which are discussed in Summarizing, Grouping, and Sorting Query Results.

     For a complete listing of what expressions can consist of, see Expressions.

The following sections provide examples of the kinds of expressions you can use in a select list.

  Selecting all columns from a table

The asterisk (*) has a special meaning in SELECT statements. It stands for all the column names in all the tables specified by the FROM clause. You can use it to save typing time and errors when you want to see all the columns in a table.

When you use SELECT *, the columns are returned in the order in which they were defined when the table was created

The syntax for selecting all the columns in a table is:

SELECT * 
FROM table-expression

SELECT * finds all the columns currently in a table, so that changes in the structure of a table such as adding, removing, or renaming columns automatically modify the results of SELECT *. Listing the columns individually gives you more precise control over the results.

Example 

The following statement retrieves all columns in the department table. No WHERE clause is included; and so this statement retrieves every row in the table:

SELECT * 
FROM department

The results look like this:

dept_id	dept_name	dept_head_id
100	R & D	501
200	Sales	904
300	Finance	1293
400	Marketing	1576
500	Shipping	703

You get exactly the same results by listing all the column names in the table in order after the SELECT keyword:

SELECT dept_id, dept_name, dept_head_id
FROM department

Like a column name, "*" can be qualified with a table name, as in the following query:

SELECT department.* 
FROM department

  Selecting specific columns from a table

To SELECT only specific columns in a table, use this syntax:

SELECT column_name [, column_name ]...
FROM table-name

You must separate each column name from the column name that follows it with a comma, for example:

SELECT emp_lname, emp_fname
FROM employee

Rearranging the order of columns 

The order in which you list the column names determines the order in which the columns are displayed. The two following examples show how to specify column order in a display. Both of them find and display the department names and identification numbers from all five of the rows in the department table, but in a different order.

SELECT dept_id, dept_name 
FROM department

dept_id	dept_name
100	R & D
200	Sales
300	Finance
400	Marketing
500	Shipping

SELECT dept_name, dept_id 
FROM department

dept_name	dept_id
R & D	100
Sales	200
Finance	300
Marketing	400
Shipping	500

  Renaming columns using aliases in query results

Query results consist of a set of columns. By default, the heading for each column is the expression supplied in the select list.

When query results are displayed, each column's default heading is the name given to it when it was created. You can specify a different column heading, or alias, in one of the following ways:

SELECT column-name AS alias

SELECT column-name alias

SELECT alias = column-name

Providing an alias can produce more readable results. For example, you can change dept_name to Department in a listing of departments as follows:

SELECT dept_name AS Department, 
   dept_id AS "Identifying Number"
FROM department

Department	Identifying Number
R & D	100
Sales	200
Finance	300
Marketing	400
Shipping	500

Using spaces and keywords in alias 

The Identifying Number alias for dept_id is enclosed in double quotes because it is an identifier. You also use double quotes if you wish to use keywords in aliases. For example, the following query is invalid without the quotation marks:

SELECT dept_name AS Department, 
   dept_id AS "integer"
FROM department

If you wish to ensure compatibility with Adaptive Server Enterprise, you should use quoted aliases of 30 bytes or less.

  Character strings in query results

The SELECT statements you have seen so far produce results that consist solely of data from the tables in the FROM clause. Strings of characters can also be displayed in query results by enclosing them in single quotation marks and separate them from other elements in the select list with commas.

To enclose a quotation mark in a string, you precede it with another quotation mark.

For example:

SELECT 'The department''s name is' AS " ", 
   Department = dept_name 
FROM department

	Department
The department's name is	R & D
The department's name is	Sales
The department's name is	Finance
The department's name is	Marketing
The department's name is	Shipping

  Computing values in the select list

The expressions in the select list can be more complicated than just column names or strings. For example, you can perform computations with data from numeric columns in a select list.

Arithmetic operations 

To illustrate the numeric operations you can carry out in the select list, we start with a listing of the names, quantity in stock, and unit price of products in the sample database. The number of zeroes in the unit_price column is truncated for readability.

SELECT name, quantity, unit_price
FROM product

name	quantity	unit_price
Tee Shirt	28	9.00
Tee Shirt	54	14.00
Tee Shirt	75	14.00
Baseball Cap	112	9.00
Baseball Cap	12	10.00
Visor	36	7.00
Visor	28	7.00
Sweatshirt	39	24.00
Sweatshirt	32	24.00
Shorts	80	15.00

Suppose the practice is to replenish the stock of a product when there are ten items left in stock. The following query lists the number of each product that must be sold before re-ordering:

SELECT name, quantity - 10 
   AS "Sell before reorder"
FROM product

name	Sell before reorder
Tee Shirt	18
Tee Shirt	44
Tee Shirt	65
Baseball Cap	102
Baseball Cap	2
Visor	26
Visor	18
Sweatshirt	29
Sweatshirt	22
Shorts	70

You can also combine the values in columns. The following query lists the total value of each product in stock:

SELECT name, 
   quantity * unit_price AS "Inventory value"
FROM product

name	Inventory value
Tee Shirt	252.00
Tee Shirt	756.00
Tee Shirt	1050.00
Baseball Cap	1008.00
Baseball Cap	120.00
Visor	252.00
Visor	196.00
Sweatshirt	936.00
Sweatshirt	768.00
Shorts	1200.00

Arithmetic operator precedence 

When there is more than one arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. When all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions within parentheses take precedence over all other operations.

For example, the following SELECT statement calculates the total value of each product in inventory, and then subtracts five dollars from that value.

SELECT name, quantity * unit_price - 5
FROM product

To avoid misunderstandings, it is recommended that you use parentheses. The following query has the same meaning and gives the same results as the previous one, but some may find it easier to understand:

SELECT name, ( quantity * unit_price ) - 5
FROM product

String operations 

You can concatenate strings using a string concatenation operator. You can use either || (SQL/92 compliant) or + (supported by Adaptive Server Enterprise) as the concatenation operator.

The following example illustrates the use of the string concatenation operator in the select list:

SELECT emp_id, emp_fname || ' ' || emp_lname AS Name 
FROM employee

emp_id	Name
102	Fran Whitney
105	Matthew Cobb
129	Philip Chin
148	Julie Jordan
...	...

Date and time operations 

Although you can use operators on date and time columns, this typically involves the use of functions. For information on SQL functions, see SQL Functions.

  Eliminating duplicate query results

The optional DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement.

If you do not specify DISTINCT, you get all rows, including duplicates. Optionally, you can specify all before the select list to get all rows. For compatibility with other implementations of SQL, Adaptive Server syntax allows the use of ALL to explicitly ask for all rows. ALL is the default.

For example, if you search for all the cities in the contact table without DISTINCT, you get 60 rows:

SELECT city
FROM contact

You can eliminate the duplicate entries using DISTINCT. The following query returns only 16 rows.:

SELECT DISTINCT city
FROM contact

NULL values are not distinct 

The DISTINCT keyword treats NULL values as duplicates of each other. In other words, when DISTINCT is included in a SELECT statement, only one NULL is returned in the results, no matter how many NULL values are encountered.