- SELECT Overview and Syntax
- Choosing Columns: The SELECT Clause
- Specifying Tables: The FROM Clause
- Selecting Rows: The WHERE Clause
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Choosing Columns: The SELECT Clause
The first clause of the SELECT statement—the one that begins with the keyword SELECT—is required in all SELECT statements. The keywords ALL and DISTINCT, which specify whether duplicate rows are to be included in the results, are optional. DISTINCT and ALL are discussed in the next chapter.
The select_list specifies the columns you want to see in the results. It can consist of these items individually or together:
An asterisk, shorthand for all the columns in the table, displayed in CREATE TABLE order
One or more column names, in any order
One or more character constants (such as "Total") used as display headings or text embedded in the results
One or more SQL functions (AVG) and arithmetic operators, generally used with columns (price * 1.085)
You can mix these elements freely. As mentioned earlier, columns, constants, functions, and combinations of these elements, with or without arithmetic operators, are collectively called expressions. Separate with a comma each element in a SELECT list from the following element.
Choosing All Columns: SELECT *
The asterisk (*) has a special meaning in the select_list. It stands for all the column names in all the tables in the table list. The columns are displayed in the order in which they appeared in the CREATE TABLE statement(s). Most people read a SELECT * statement as "select star." Use it when you want to see all the columns in a table.
The general syntax for selecting all the columns in a table is this:
SYNTAX
SELECT * FROM table/view_list
Because SELECT * finds all the columns currently in a table, changes in the structure of a table (adding, removing, or renaming columns) automatically modify the results of a SELECT *. Listing the columns individually gives you more precise control over the results, but SELECT * saves typing (and the frustration of typographical errors). SELECT * is most useful for tables with few columns because displays of many columns can be confusing. It also comes in handy when you want to get a quick look at a table's structure (what columns it has and in what order they appear).
The following statement retrieves all columns in the publishers table and displays them in the order in which they were defined when the publishers table was created. Because no WHERE clause is included, this statement retrieves every row.
SQL
select * from publishers pub_id pub_name address city state ====== ====================== ============= ============ ===== 0736 New Age Books 1 1st St. Boston MA 0877 Binnet & Hardley 2 2nd Ave. Washington DC 1389 Algodata Infosystems 3 3rd Dr. Berkeley CA [3 rows]
You get exactly the same results by listing all the column names in the table in CREATE TABLE order after the SELECT keyword:
SQL
select pub_id, pub_name, address, city, state from publishers pub_id pub_name address city state ====== ====================== ================= ============ ===== 0736 New Age Books 1 1st St. Boston MA 0877 Binnet & Hardley 2 2nd Ave. Washington DC 1389 Algodata Infosystems 3 3rd Dr. Berkeley CA [3 rows]
Choosing Specific Columns
To select a subset of the columns in a table, as some of the previous examples have demonstrated, simply list the columns you want to see in the SELECT list:
SYNTAX
SELECT column_name[, column_name]... FROM table_list
Separate each column name from the following column name with a comma.
Rearranging Result Columns
The order in which columns appear in a display is completely up to you: Use the SELECT list to order them in any way that makes sense.
Following are two examples. Both of them find and display the publisher names and identification numbers from all three of the rows in the publishers table. The first one prints pub_id first, followed by pub_name. The second reverses that order. The information is exactly the same; only the display format changes.
SQL
select pub_id, pub_name from publishers pub_id pub_name ====== ======================================== 0736 New Age Books 0877 Binnet & Hardley 1389 Algodata Infosystems [3 rows]
select pub_name, pub_id from publishers pub_name pub_id ======================================== ====== New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389 [3 rows]
More Than Column Names
The SELECT statements you've seen so far show exactly what's stored in a table. This is useful, but often not useful enough. SQL lets you add to and manipulate these results to make them easier to read or to do "what if" queries. This means you can use strings of characters, mathematical calculations, and functions provided by your system in the SELECT list, with or without column names.
Display Label Conventions
When the results of a query are displayed, each column has a default heading—its name as defined in the database. Column names in databases are often cryptic (so they'll be easy to type) or have no meaning to users unfamiliar with departmental acronyms, nicknames, or project jargon.
You can solve this problem by specifying display labels (sometimes called column aliases or headings) to make query results easier to read and understand. To get the heading you want, simply type column_name column_heading, or column_name as column_heading in the SELECT clause in place of the column name. For example, to change the pub_name column heading to Publisher, try one of the following statements:
SQL
select pub_name Publisher, pub_id from publishers
SQL
select pub_name as Publisher, pub_id from publishers
Some systems also allow this syntax:
Adaptive Server Anywhere
select Publisher = pub_name, pub_id from publishers
The results of all three methods show a new column heading:
Results
Publisher pub_id ======================================== ====== New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389 [3 rows]
TIP
For consistency, pick one of these formats and stick with it. Many users prefer the AS convention—it has the advantage of being simple and unambiguous.
SQL Variants
Check to see how your system handles column headings that are longer than defined column size. For example, what happens when you change the pub_id column heading to a string such as "Identification #"? Does your system increase the display size of the column or shorten the new column heading to the size of the column data? The following queries show two possibilities:
Adaptive Server Anywhere
select pub_name as Publisher, pub_id as Identification# from publishers Publisher Identification# ======================================== =============== New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389 [3 rows]
Oracle
PUBLISHER IDEN ---------------------------------------- ---- New Age Books 0736 Binnet + Hardley 0877 Algodata Infosystems 1389
(Oracle SQL Plus shows display headings as uppercase by default. Enclose the heading text in double quotes to preserve case.) If you use a smaller heading, however, SQL doesn't shrink the display size to less than its datatype-defined size.
Display Label Limitations
Most SQL dialects that allow you to add display labels have some restrictions. Check your reference guide for details on
Quotes (single and double)
Embedded spaces
Special characters
For example, Adaptive Server Anywhere allows single and double quotes around column headings. The quotes are not needed unless there is an embedded space in the column heading.
Adaptive Server Anywhere
select pub_name as 'Publisher #', pub_id as "Identification #" from publishers; Publisher # Identification # ======================================== ================ New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389
However, other systems are not as forgiving.
SQL Variants
Oracle SQL Plus rejects single quotes around column headings.
Oracle
SQL> select pub_name as Publisher, pub_id as 'Identification #' 2 from publishers; ERROR at line 1: ORA-00923: FROM keyword not found where expected
Change the single quotes to double, and the query works fine. In addition, the original case of the heading is preserved.
Oracle
SQL> select pub_name as "Publisher #", pub_id as "Identification #" 2 from publishers; Publisher # Iden ---------------------------------------- ---- New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389
Other implementations object to spaces or special characters.
Informix
select pub_name as Publisher, pub_id as Identification# from publishers SQL Error. An illegal character has been found.
The illegal character is the pound sign (#). Quotation marks don't help in this case.
Character Strings in Query Results
Sometimes a little text can make query results easier to understand. That's where strings (of characters) come in handy.
Let's say you want a listing of publishers with something like "The publisher's name is" in front of each item. All you have to do is insert the string in the correct position in the SELECT list. Be sure to enclose the entire string in quotes (single quotes are standard, but some dialects allow both single and double quotes) so your system can tell it's not a column name and separate it with commas from other elements in the select_list .
Follow your system's rules for protecting embedded apostrophes and quotes, if any appear in the string. In most cases, double single quotes do the trick and prevent the apostrophe from being interpreted as a close quote.
SQL
select 'The publisher''s name is', pub_name as Publisher from publishers 'The publisher''s name is' Publisher ========================== ============================ The publisher's name is New Age Books The publisher's name is Binnet & Hardley The publisher's name is Algodata Infosystems [3 rows]
The constants create a new column in the display only—what you see doesn't affect anything that's physically in the database.
Combining Columns, Display Headings, and Text
You can combine columns, display headings, and text in a SELECT list.
Remember to put quotes around the text but not around the column names. You need quotes around display headings only if they contain spaces (or other special characters). Figure 4.3 illustrates mixing several techniques.
){kind=link}
Figure 4.3 Column Names, Text, and Display Headings
Computations with Constants
The SELECT list is the place where you indicate computations you want to perform on numeric data or constants.
Here are the available arithmetic operators:
|
Symbol |
Operation |
|
+ |
addition |
|
- |
subtraction |
|
/ |
division |
|
* |
multiplication |
The arithmetic operators—addition, subtraction, division, and multiplication—can be used on any numeric column.
Certain arithmetic operations can also be performed on date columns, if your system provides date functions.
You can use all of these operators in the SELECT list with column names and numeric constants in any combination. For example, to see what a projected sales increase of 100 percent for all the books in the titles table looks like, type this:
SQL
select title_id, ytd_sales, ytd_sales * 2 from titles title_id ytd_sales titles.ytd_sales*2 ======== =========== ================== PC8888 4095 8190 BU1032 4095 8190 PS7777 3336 6672 PS3333 4072 8144 BU1111 3876 7752 MC2222 2032 4064 TC7777 4095 8190 TC4203 15096 30192 PC1035 8780 17560 BU2075 18722 37444 PS2091 2045 4090 PS2106 111 222 MC3021 22246 44492 TC3218 375 750 MC3026 (NULL) (NULL) BU7832 4095 8190 PS1372 375 750 PC9999 (NULL) (NULL) [18 rows]
Notice the null values in the ytd_sales column and the computed column. When you perform any arithmetic operation on a null value, the result is NULL.
SQL Variants
The null value may show up as a blank, as the word NULL, or as some other symbol determined by the system. Check your vendor's documentation: You may have a way to change the default NULL display.
Oracle
SQL> select title_id, ytd_sales, ytd_sales * 2 2 from titles 3 where title_id > 'M' and title_id < 'PS'; TITLE_ YTD_SALES YTD_SALES*2 ------ --------- ----------- MC2222 2032 4064 MC3021 22246 44492 MC3026 PC1035 8780 17560 PC8888 4095 8190 PC9999 6 rows selected.
Computed Column Display Headings
You can give the computed column a heading (for example, Projected_Sales):
SQL
select title_id, ytd_sales, ytd_sales * 2 as Projected_Sales from titles
For a fancier display, try adding character strings such as "Current sales =" and "Projected sales are" to the SELECT statement.
Sometimes, as in the previous example, you'll want both the original data and the computed data in your results. But you don't have to include the column on which the computation takes place in the SELECT list. To see just the computed values, type this:
SQL
select title_id, ytd_sales * 2 from titles title_id titles.ytd_sales*2 ======== ================== PC8888 8190 BU1032 8190 PS7777 6672 PS3333 8144 BU1111 7752 MC2222 4064 TC7777 8190 TC4203 30192 PC1035 17560 BU2075 37444 PS2091 4090 PS2106 222 MC3021 44492 TC3218 750 MC3026 (NULL) BU7832 8190 PS1372 750 PC9999 (NULL) [18 rows]
Computations with Column Names
You can also use arithmetic operators for computations on the data in two or more columns, with no constants involved. Here's an example:
SQL
select title_id, ytd_sales * price from titles title_id titles.ytd_sales*titles.price ======== ================================ PC8888 81900.00 BU1032 81859.05 PS7777 26654.64 PS3333 81399.28 BU1111 46318.20 MC2222 40619.68 TC7777 61384.05 TC4203 180397.20 PC1035 201501.00 BU2075 55978.78 PS2091 22392.75 PS2106 777.00 MC3021 66515.54 TC3218 7856.25 MC3026 (NULL) BU7832 81859.05 PS1372 8096.25 PC9999 (NULL) [18 rows]
Finally, you can compute new values on the basis of columns from more than one table. (Chapter 7, on joining, and Chapter 8, on subqueries, give information on how to work with multiple-table queries, so check them for details.)
Arithmetic Operator Precedence
When there is more than one arithmetic operator in an expression, the system follows rules that determine the order in which the operations are carried out (Figure 4.4). According to commonly used precedence rules, multiplication and division are calculated first, followed by subtraction and addition. When more than one arithmetic operator in an expression has the same level of precedence, the order of execution is left to right. Expressions within parentheses take precedence over all other operations.
){kind=link}
Figure 4.4 Precedence Hierarchy for Arithmetic Operators
Here's an example: The following SELECT statement subtracts the advance on each book from the gross revenues realized on its sales (price multiplied by ytd_sales). The product of ytd_sales and price is calculated first because the operator is multiplication.
SQL
select title_id, ytd_sales * price - advance from titles
To avoid misunderstandings, use parentheses. The following query has the same meaning and gives the same results as the previous one, but it is easier to understand:
SQL
select title_id, (ytd_sales * price) - advance from titles title_id titles.ytd_sales*titles.price ======== ============================= PC8888 155800.00 BU1032 117809.05 PS7777 56014.64 PS3333 120119.28 BU1111 80078.20 MC2222 60939.68 TC7777 114809.05 TC4203 327357.20 PC1035 370101.00 BU2075 233073.78 PS2091 42612.75 PS2106 -4113.00 MC3021 273975.54 TC3218 8356.25 MC3026 (NULL) BU7832 117809.05 PS1372 8596.25 PC9999 (NULL) [18 rows]
Another important use of parentheses is changing the order of execution: Calculations inside parentheses are handled first. If parentheses are nested (one set of parentheses inside another), the most deeply nested calculation has precedence. For example, the result and meaning of the query just shown can be changed if you use parentheses to force evaluation of the subtraction before the multiplication:
SQL
select title_id, ytd_sales * (price - advance) from titles title_id titles.ytd_sales*(titles.pric ======== ============================= PC8888 -32596200.00 BU1032 -20352190.95 PS7777 -13283985.36 PS3333 -8021880.72 BU1111 -19294921.80 MC2222 60939.68 TC7777 -32637190.95 TC4203 -60052642.80 PC1035 -61082899.00 BU2075 -189317051.22 PS2091 -4607487.25 PS2106 -664113.00 MC3021 -333401024.46 TC3218 -2609643.75 MC3026 (NULL) BU7832 -20352190.95 PS1372 -2609403.75 PC9999 (NULL) [18 rows]