Home > Articles > Data > SQL

  • Print
  • + Share This
  • 💬 Discuss
From the author of

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.

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.

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]
  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus