Home > Articles > Data > SQL

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.

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]

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020