Transact-SQL Reference

SELECT Examples

A. Use SELECT to retrieve rows and columns

This example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the authors table in the pubs database.

USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC

-- Alternate way.
USE pubs
SELECT authors.*
FROM customers
ORDER BY au_lname ASC, au_fname ASC

This example returns all rows (no WHERE clause is specified), and only a subset of the columns (au_lname, au_fname, phone, city, state) from the authors table in the pubs database. In addition, column headings are added.

USE pubs
SELECT au_fname, au_lname, phone AS Telephone, city, state
FROM authors
ORDER BY au_lname ASC, au_fname ASC

This example returns only the rows for authors who live in California and do not have the last name McBadden.

USE pubs
SELECT au_fname, au_lname, phone AS Telephone
FROM authors
WHERE state = 'CA' and au_lname <> 'McBadden'
ORDER BY au_lname ASC, au_fname ASC
B. Use SELECT with column headings and calculations

These examples return all rows from titles. The first example returns total year-to-date sales and the amounts due to each author and publisher. In the second example, the total revenue is calculated for each book.

USE pubs
SELECT ytd_sales AS Sales, 
   authors.au_fname + ' '+ authors.au_lname AS Author, 
   ToAuthor = (ytd_sales * royalty) / 100,
   ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles INNER JOIN titleauthor
   ON titles.title_id = titleauthor.title_id INNER JOIN authors
   ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC

Here is the result set:

Sales       Author                    ToAuthor    ToPublisher 
----------- ------------------------- ----------- ----------- 
22246       Anne Ringer               5339        16907
22246       Michel DeFrance           5339        16907
18722       Marjorie Green            4493        14229
15096       Reginald Blotchet-Halls   2113        12983
8780        Cheryl Carson             1404        7376
4095        Abraham Bennet            409         3686
4095        Akiko Yokomoto            409         3686
4095        Ann Dull                  409         3686
4095        Burt Gringlesby           409         3686
4095        Dean Straight             409         3686
4095        Marjorie Green            409         3686
4095        Michael O'Leary           409         3686
4095        Sheryl Hunter             409         3686
4072        Johnson White             407         3665
3876        Michael O'Leary           387         3489
3876        Stearns MacFeather        387         3489
3336        Charlene Locksley         333         3003
2045        Albert Ringer             245         1800
2045        Anne Ringer               245         1800
2032        Innes del Castillo        243         1789
375         Livia Karsen              37          338
375         Stearns MacFeather        37          338
375         Sylvia Panteley           37          338
111         Albert Ringer             11          100
NULL        Charlene Locksley         NULL        NULL

(25 row(s) affected)

This is the query that calculates the revenue for each book:

USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue, 
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC

Here is the result set:

Revenue                                    Book#  
--------------- --------------------- ---- ------ 
Total income is 81859.0500            for  BU1032
Total income is 46318.2000            for  BU1111
Total income is 55978.7800            for  BU2075
Total income is 81859.0500            for  BU7832
Total income is 40619.6800            for  MC2222
Total income is 66515.5400            for  MC3021
Total income is NULL                  for  MC3026
Total income is 201501.0000           for  PC1035
Total income is 81900.0000            for  PC8888
Total income is NULL                  for  PC9999
Total income is 8096.2500             for  PS1372
Total income is 22392.7500            for  PS2091
Total income is 777.0000              for  PS2106
Total income is 81399.2800            for  PS3333
Total income is 26654.6400            for  PS7777
Total income is 7856.2500             for  TC3218
Total income is 180397.2000           for  TC4203
Total income is 61384.0500            for  TC7777

(18 row(s) affected)
C. Use DISTINCT with SELECT

This example uses DISTINCT to prevent the retrieval of duplicate author ID numbers.

USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_id
D. Create tables with SELECT INTO

This first example creates a temporary table named #coffeetabletitles in tempdb. To use this table, always refer to it with the exact name shown, including the number sign (#).

USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name 
FROM tempdb..sysobjects 
WHERE name LIKE '#c%'

Here is the result set:

name                                                                    
------------------------------------------------------------------------
#coffeetabletitles__________________________________________________________________________________________________000000000028

(1 row(s) affected)

CHECKPOINTing database that was changed.

(12 row(s) affected)

name                                                                    
------------------------------------------------------------------------
newtitles

(1 row(s) affected)

CHECKPOINTing database that was changed.

This second example creates a permanent table named newtitles.

USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
      WHERE table_name = 'newtitles')
   DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

Here is the result set:

name                           
------------------------------ 
newtitles                      

(1 row(s) affected)
E. Use correlated subqueries

This example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery retrieving one instance of each publisher name for which the book title is a business book, and the publisher ID numbers match between the titles and publishers tables.

USE pubs
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')

-- Or
USE pubs
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')

This example uses IN in a correlated (or repeating) subquery, which is a query that depends on the outer query for its values. It is executed repeatedly, once for each row that may be selected by the outer query. This query retrieves one instance of each author's first and last name for which the royalty percentage in the titleauthor table is 100 and for which the author identification numbers match in the authors and titleauthor tables.

USE pubs
SELECT DISTINCT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_id = authors.au_id)

The above subquery in this statement cannot be evaluated independently of the outer query. It needs a value for authors.au_id, but this value changes as Microsoft® SQL Server™ examines different rows in authors.

A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the types of books for which the maximum advance is more than twice the average for the group.

USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
   (SELECT 2 * AVG(t2.advance)
   FROM titles t2
   WHERE t1.type = t2.type)

This example uses two correlated subqueries to find the names of authors who have participated in writing at least one popular computing book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
   (SELECT au_id
   FROM titleauthor
   WHERE title_id IN
      (SELECT title_id
      FROM titles
      WHERE type = 'popular_comp'))
F. Use GROUP BY

This example finds the total year-to-date sales of each publisher in the database.

USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
ORDER BY pub_id

Here is the result set:

pub_id   total
------   -----
0736      28286
0877      44219
1389      24941

(3 row(s) affected)

Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each publisher.

G. Use GROUP BY with multiple groups

This example finds the average price and the sum of year-to-date sales, grouped by type and publisher ID.

USE pubs
SELECT type, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'
FROM titles
GROUP BY type, pub_id
ORDER BY type, pub_id

Here is the result set:

type         pub_id avg                   sum         
------------ ------ --------------------- ----------- 
business     0736   2.9900                18722
business     1389   17.3100               12066
mod_cook     0877   11.4900               24278
popular_comp 1389   21.4750               12875
psychology   0736   11.4825               9564
psychology   0877   21.5900               375
trad_cook    0877   15.9633               19566
UNDECIDED    0877   NULL                  NULL

(8 row(s) affected)

Warning, null value eliminated from aggregate.
H. Use GROUP BY and WHERE

This example puts the results into groups after retrieving only the rows with advances greater than $5,000.

USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type
ORDER BY type

Here is the result set:

type                                    
------------ -------------------------- 
business     2.99                       
mod_cook     2.99                       
popular_comp 21.48                      
psychology   14.30                      
trad_cook    17.97                      

(5 row(s) affected)
I. Use GROUP BY with an expression

This example groups by an expression. You can group by an expression if the expression does not include aggregate functions.

USE pubs
SELECT AVG(ytd_sales), ytd_sales * royalty
FROM titles
GROUP BY ytd_sales * royalty
ORDER BY ytd_sales * royalty

Here is the result set:

----------- ----------- 
NULL        NULL      
111         1110        
375         3750        
2032        24384       
2045        24540       
3336        33360       
3876        38760       
4072        40720       
4095        40950       
8780        140480      
15096       211344      
18722       449328      
22246       533904      

(13 row(s) affected)
J. Compare GROUP BY and GROUP BY ALL

The first example produces groups only for those books that commanded royalties of 10 percent. Because no modern cookbooks have a royalty of 10 percent, there is no group in the results for the mod_cook type.

The second example produces groups for all types, including modern cookbooks and UNDECIDED, although the modern cookbook group does not include any rows that meet the qualification specified in the WHERE clause.

The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.

USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY type
ORDER BY type

Here is the result set:

type                                    
------------ -------------------------- 
business     17.31                      
popular_comp 20.00                      
psychology   14.14                      
trad_cook    17.97                      

(4 row(s) affected)

-- Using GROUP BY ALL
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE royalty = 10
GROUP BY all type
ORDER BY type

Here is the result set:

type                                    
------------ -------------------------- 
business     17.31                      
mod_cook     NULL                     
popular_comp 20.00                      
psychology   14.14                      
trad_cook    17.97                      
UNDECIDED    NULL                     

(6 row(s) affected)
K. Use GROUP BY with ORDER BY

This example finds the average price of each type of book and orders the results by average price.

USE pubs
SELECT type, AVG(price)
FROM titles
GROUP BY type
ORDER BY AVG(price)

Here is the result set:

type                                    
------------ -------------------------- 
UNDECIDED    NULL                     
mod_cook     11.49                      
psychology   13.50                      
business     13.73                      
trad_cook    15.96                      
popular_comp 21.48                      

(6 row(s) affected)
L. Use the HAVING clause

The first example shows a HAVING clause with an aggregate function. It groups the rows in the titles table by type and eliminates the groups that include only one book. The second example shows a HAVING clause without aggregate functions. It groups the rows in the titles table by type and eliminates those types that do not start with the letter p.

USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1
ORDER BY type

Here is the result set:

type         
------------ 
business     
mod_cook     
popular_comp 
psychology   
trad_cook    

(5 row(s) affected)

This query uses the LIKE clause in the HAVING clause.

USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'
ORDER BY type

Here is the result set:

type
------------
popular_comp
psychology

(2 row(s) affected)
M. Use HAVING and GROUP BY

This example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the titles with prices under $5. It also organizes the results by pub_id.

USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
WHERE price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
   AND AVG(price) < $20
   AND pub_id > '0800'
ORDER BY pub_id

Here is the result set:

pub_id                                                       
------ -------------------------- -------------------------- 
0877   26,000.00                  17.89                      
1389   30,000.00                  18.98                      

(2 row(s) affected)
N. Use HAVING with SUM and AVG

This example groups the titles table by publisher and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price.

USE pubs
SELECT pub_id, SUM(advance), AVG(price)
FROM titles
GROUP BY pub_id
HAVING SUM(advance) > $25000 
AND AVG(price) > $15

To see the publishers who have had year-to-date sales greater than $40,000, use this query:

USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000

If you want to make sure there are at least six books involved in the calculations for each publisher, use HAVING COUNT(*) > 5 to eliminate the publishers that return totals for fewer than six books. The query looks like this:

USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5

Here is the result set:

pub_id   total
------   -----
0877      44219
1389      24941
   
(2 row(s) affected)

With this statement, two rows are returned. New Moon Books (0736) is eliminated.

O. Calculate group totals with COMPUTE BY

This example uses two code examples to show the use of COMPUTE BY. The first code example uses one COMPUTE BY with one aggregate function, and the second code example uses one COMPUTE BY item and two aggregate functions.

This example calculates the sum of the prices (for prices over $10) for each type of cookbook, in order first by type of book and then by price of book.

USE pubs
SELECT type, price
FROM titles
WHERE price > $10
   AND type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type

Here is the result set:

type         price                 
------------ --------------------- 
mod_cook     19.9900

(1 row(s) affected)

sum                   
--------------------- 
19.9900

(1 row(s) affected)

type         price                 
------------ --------------------- 
trad_cook    11.9500
trad_cook    14.9900
trad_cook    20.9500

(3 row(s) affected)

sum                   
--------------------- 
47.8900

(1 row(s) affected)

This example retrieves the book type, publisher identification number, and price of all cookbooks. The COMPUTE BY clause uses two different aggregate functions.

USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, pub_id
COMPUTE SUM(price), MAX(pub_id) BY type

Here is the result set:

type         pub_id price                 
------------ ------ --------------------- 
mod_cook     0877   19.9900
mod_cook     0877   2.9900

(2 row(s) affected)

sum                   max  
--------------------- ---- 
22.9800               0877

(1 row(s) affected)

type         pub_id price                 
------------ ------ --------------------- 
trad_cook    0877   20.9500
trad_cook    0877   11.9500
trad_cook    0877   14.9900

(3 row(s) affected)

sum                   max  
--------------------- ---- 
47.8900               0877

(1 row(s) affected)
P. Calculate grand values using COMPUTE without BY

The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on.

This statement finds the grand total of the prices and advances for all types of books over $20.

USE pubs
SELECT type, price, advance
FROM titles
WHERE price > $20
COMPUTE SUM(price), SUM(advance)

You can use COMPUTE BY and COMPUTE without BY in the same query. This query finds the sum of prices and advances by type, and then computes the grand total of prices and advances for all types of books.

USE pubs
SELECT type, price, advance
FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)

Here is the result set:

type         price                 advance               
------------ --------------------- --------------------- 
mod_cook     2.9900                15000.0000
mod_cook     19.9900               .0000

(2 row(s) affected)

sum                   sum                   
--------------------- --------------------- 
22.9800               15000.0000

(1 row(s) affected)

type         price                 advance               
------------ --------------------- --------------------- 
trad_cook    11.9500               4000.0000
trad_cook    14.9900               8000.0000
trad_cook    20.9500               7000.0000

(3 row(s) affected)

sum                   sum                   
--------------------- --------------------- 
47.8900               19000.0000

(1 row(s) affected)

sum                   sum                   
--------------------- --------------------- 
70.8700               34000.0000

(1 row(s) affected)
Q. Calculate computed sums on all rows

This example shows only three columns in the select list and gives totals based on all prices and all advances at the end of the results.

USE pubs
SELECT type, price, advance
FROM titles
COMPUTE SUM(price), SUM(advance)

Here is the result set:

type         price                 advance               
------------ --------------------- --------------------- 
business     19.9900               5000.0000
business     11.9500               5000.0000
business     2.9900                10125.0000
business     19.9900               5000.0000
mod_cook     19.9900               .0000
mod_cook     2.9900                15000.0000
UNDECIDED    NULL                  NULL
popular_comp 22.9500               7000.0000
popular_comp 20.0000               8000.0000
popular_comp NULL                  NULL
psychology   21.5900               7000.0000
psychology   10.9500               2275.0000
psychology   7.0000                6000.0000
psychology   19.9900               2000.0000
psychology   7.9900                4000.0000
trad_cook    20.9500               7000.0000
trad_cook    11.9500               4000.0000
trad_cook    14.9900               8000.0000

(18 row(s) affected)

sum                   sum                   
--------------------- --------------------- 
236.2600              95400.0000

(1 row(s) affected)

Warning, null value eliminated from aggregate.
R. Use more than one COMPUTE clause

This example finds the sum of the prices of all psychology books, as well as the sum of the prices of psychology books organized by publisher. You can use different aggregate functions in the same statement by including more than one COMPUTE BY clause.

USE pubs
SELECT type, pub_id, price
FROM titles
WHERE type = 'psychology'
ORDER BY type, pub_id, price   
COMPUTE SUM(price) BY type, pub_id
COMPUTE SUM(price) BY type

Here is the result set:

type         pub_id price                 
------------ ------ --------------------- 
psychology   0736   7.0000
psychology   0736   7.9900
psychology   0736   10.9500
psychology   0736   19.9900

(4 row(s) affected)

sum                   
--------------------- 
45.9300

(1 row(s) affected)

type         pub_id price                 
------------ ------ --------------------- 
psychology   0877   21.5900

(1 row(s) affected)

sum                   
--------------------- 
21.5900

(1 row(s) affected)

sum                   
--------------------- 
67.5200

(1 row(s) affected)
S. Compare GROUP BY with COMPUTE

The first example uses the COMPUTE clause to calculate the sum for the prices of the different types of cookbooks. The second example produces the same summary information using only GROUP BY.

USE pubs
-- Using COMPUTE
SELECT type, price
FROM titles
WHERE type like '%cook'
ORDER BY type, price   
COMPUTE SUM(price) BY type

Here is the result set:

type         price                 
------------ --------------------- 
mod_cook     2.9900
mod_cook     19.9900

(2 row(s) affected)

sum                   
--------------------- 
22.9800

(1 row(s) affected)

type         price                 
------------ --------------------- 
trad_cook    11.9500
trad_cook    14.9900
trad_cook    20.9500

(3 row(s) affected)

sum                   
--------------------- 
47.8900

(1 row(s) affected)

This is the second query using GROUP BY:

USE pubs
-- Using GROUP BY
SELECT type, SUM(price)
FROM titles
WHERE type LIKE '%cook'
GROUP BY type
ORDER BY type

Here is the result set:

type                               
------------ --------------------- 
mod_cook     22.9800
trad_cook    47.8900

(2 row(s) affected)
T. Use SELECT with GROUP BY, COMPUTE, and ORDER BY clauses

This example returns only those rows with current year-to-date sales, and then computes the average book cost and total advances in descending order by type. Four columns of data are returned, including a truncated title. All computed columns appear within the select list.

USE pubs
SELECT CAST(title AS char(20)) AS title, type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)

Here is the result set:

title                type         price                 advance               
-------------------- ------------ --------------------- ----------------
Onions, Leeks, and G trad_cook    20.9500               7000.0000
Fifty Years in Bucki trad_cook    11.9500               4000.0000
Sushi, Anyone?       trad_cook    14.9900               8000.0000

(3 row(s) affected)

avg                   sum                   
--------------------- --------------------- 
15.9633               19000.0000

(1 row(s) affected)

title                type         price                 advance               
-------------------- ------------ --------------------- ----------------
Computer Phobic AND  psychology   21.5900               7000.0000
Is Anger the Enemy?  psychology   10.9500               2275.0000
Life Without Fear    psychology   7.0000                6000.0000
Prolonged Data Depri psychology   19.9900               2000.0000
Emotional Security:  psychology   7.9900                4000.0000

(5 row(s) affected)

avg                   sum                   
--------------------- --------------------- 
13.5040               21275.0000

(1 row(s) affected)

title                type         price                 advance               
-------------------- ------------ --------------------- ----------------
But Is It User Frien popular_comp 22.9500               7000.0000
Secrets of Silicon V popular_comp 20.0000               8000.0000

(2 row(s) affected)

avg                   sum                   
--------------------- --------------------- 
21.4750               15000.0000

(1 row(s) affected)

title                type         price                 advance               
-------------------- ------------ --------------------- ----------------
Silicon Valley Gastr mod_cook     19.9900               .0000
The Gourmet Microwav mod_cook     2.9900                15000.0000

(2 row(s) affected)

avg                   sum                   
--------------------- --------------------- 
11.4900               15000.0000

(1 row(s) affected)

title                type         price                 advance               
-------------------- ------------ --------------------- ----------------
The Busy Executive's business     19.9900               5000.0000
Cooking with Compute business     11.9500               5000.0000
You Can Combat Compu business     2.9900                10125.0000
Straight Talk About  business     19.9900               5000.0000

(4 row(s) affected)

avg                   sum                   
--------------------- --------------------- 
13.7300               25125.0000

(1 row(s) affected)

sum                   sum                   
--------------------- --------------------- 
236.2600              95400.0000

(1 row(s) affected)
U. Use SELECT statement with CUBE

This example shows two code examples. The first example returns a result set from a SELECT statement using the CUBE operator. The SELECT statement covers a one-to-many relationship between book titles and the quantity sold of each book. By using the CUBE operator, the statement returns an extra row.

USE pubs
SELECT SUBSTRING(title, 1, 65) AS title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles 
   ON sales.title_id = titles.title_id
GROUP BY title
WITH CUBE
ORDER BY title

Here is the result set:

title                                                             qty         
----------------------------------------------------------------- ------
NULL                                                              493         
But Is It User Friendly?                                          30          
Computer Phobic AND Non-Phobic Individuals: Behavior Variations   20          
Cooking with Computers: Surreptitious Balance Sheets              25          
...
The Busy Executive's Database Guide                               15          
The Gourmet Microwave                                             40          
You Can Combat Computer Stress!                                   35          

(17 row(s) affected)

NULL represents all values in the title column. The result set returns values for the quantity sold of each title and the total quantity sold of all titles. Applying the CUBE operator or ROLLUP operator returns the same result.

This example uses the cube_examples table to show how the CUBE operator affects the result set and uses an aggregate function (SUM). The cube_examples table contains a product name, a customer name, and the number of orders each customer has made for a particular product.

USE pubs
CREATE TABLE cube_examples
(product_name varchar(30)  NULL,
 customer_name varchar(30) NULL,
 number_of_orders int      NULL
)

INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Ikura', 'Wilman Kala', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Ikura', 'Wilman Kala', 40)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Filo Mix', 'Romero y tomillo', 50)

First, issue a typical query with a GROUP BY clause and the result set.

USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
ORDER BY product_name

The GROUP BY causes the result set to form groups within groups. Here is the result set:

product_name                   customer_name                              
------------------------------ ------------------------------ ----------
Filo Mix                       Eastern Connection             40          
Filo Mix                       Romero y tomillo               80          
Filo Mix                       Wilman Kala                    30          
Ikura                          Romero y tomillo               20          
Ikura                          Wilman Kala                    50          
Outback Lager                  Eastern Connection             10          
Outback Lager                  Wilman Kala                    30          

(7 row(s) affected)

Next, issue a query with a GROUP BY clause by using the CUBE operator. The result set should include the same information, and super-aggregate information for each of the GROUP BY columns.

USE pubs
SELECT product_name, customer_name, SUM(number_of_orders)
FROM cube_examples
GROUP BY product_name, customer_name
WITH CUBE

The result set for the CUBE operator holds the values from the simple GROUP BY result set above, and adds the super-aggregates for each column in the GROUP BY clause. NULL represents all values in the set from which the aggregate is computed. Here is the result set:

product_name                   customer_name                              
------------------------------ ------------------------------ ---------- 
Filo Mix                       Eastern Connection             40          
Filo Mix                       Romero y tomillo               80          
Filo Mix                       Wilman Kala                    30          
Filo Mix                       NULL                           150         
Ikura                          Romero y tomillo               20          
Ikura                          Wilman Kala                    50          
Ikura                          NULL                           70          
Outback Lager                  Eastern Connection             10          
Outback Lager                  Wilman Kala                    30          
Outback Lager                  NULL                           40          
NULL                           NULL                           260         
NULL                           Eastern Connection             50          
NULL                           Romero y tomillo               100         
NULL                           Wilman Kala                    110         

(14 row(s) affected)

Line 4 of the result set indicates that a total of 150 orders for Filo Mix was placed for all customers.

Line 11 of the result set indicates that the total number of orders placed for all products by all customers is 260.

Lines 12-14 of the result set indicate that the total number of orders for each customer for all products are 100, 110, and 50, respectively.

V. Use CUBE on a result set with three columns

This example shows two code examples. The first code example produces a CUBE result set with three columns, and the second example produces a four-column CUBE result set.

The first SELECT statement returns the publication name, title, and quantity of books sold. The GROUP BY clause in this example includes two columns called pub_name and title. There are also two one-to-many relationships between publishers and titles and between titles and sales.

By using the CUBE operator, the result set contains more detailed information about the quantities of titles sold by publishers. NULL represents all values in the title column.

USE pubs
SELECT pub_name, title, SUM(qty) AS 'qty'
FROM sales INNER JOIN titles 
   ON sales.title_id = titles.title_id INNER JOIN publishers 
   ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE

Here is the result set:

pub_name             title                                      qty 
-------------------- ---------------------------------------- ------ 
Algodata Infosystems But Is It User Friendly?                    30 
Algodata Infosystems Cooking with Computers: Surreptitious Ba    25 
Algodata Infosystems Secrets of Silicon Valley                   50 
Algodata Infosystems Straight Talk About Computers               15 
Algodata Infosystems The Busy Executive's Database Guide         15 
Algodata Infosystems NULL                                       135 
Binnet & Hardley     Computer Phobic AND Non-Phobic Individu     20 
Binnet & Hardley     Fifty Years in Buckingham Palace Kitche     20 
...                                                ...
NULL                 Sushi, Anyone?                              20 
NULL                 The Busy Executive's Database Guide         15 
NULL                 The Gourmet Microwave                       40 
NULL                 You Can Combat Computer Stress!             35 

(36 row(s) affected)

Increasing the number of columns in the GROUP BY clause shows why the CUBE operator is an n-dimensional operator. A GROUP BY clause with two columns returns three more kinds of groupings when the CUBE operator is used. The number of groupings can be more than three, depending on the distinct values in the columns.

The result set is grouped by the publisher name and then by the book title. The quantity of each title sold by each publisher is listed in the right-hand column.

NULL in the title column represents all titles. For more information about how to differentiate specific values and all values in the result set, see Example H. The CUBE operator returns these groups of information from one SELECT statement:

Each column referenced in the GROUP BY clause has been cross-referenced with all other columns in the GROUP BY clause and the SUM aggregate has been reapplied, which produces additional rows in the result set. Information returned in the result set grows n-dimensionally along with the number of columns in the GROUP BY clause.

Note  Ensure that the columns following the GROUP BY clause have meaningful, real-life relationships with each other. For example, if you use au_fname and au_lname, the CUBE operator returns irrelevant information, such as the number of books sold by authors with the same first name. Using the CUBE operator on a real-life hierarchy, such as yearly sales and quarterly sales, produces meaningless rows in the result set. It is more efficient to use the ROLLUP operator.

In this second code example, the GROUP BY clause contains three columns cross-referenced by the CUBE operator. Three one-to-many relationships exist between publishers and authors, between authors and titles, and between titles and sales.

By using the CUBE operator, more detailed information is returned about the quantities of titles sold by publishers.

USE pubs
SELECT pub_name, au_lname, title, SUM(qty)
FROM authors INNER JOIN titleauthor 
   ON authors.au_id = titleauthor.au_id INNER JOIN titles 
   ON titles.title_id = titleauthor.title_id INNER JOIN publishers 
   ON publishers.pub_id = titles.pub_id INNER JOIN sales 
   ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH CUBE

The CUBE operator returns this information based on the cross-referenced groupings returned with the CUBE operator:

Note  The super-aggregate for all publishers, all titles, and all authors is greater than the total number of sales, because a number of books have more than one author.

A pattern emerges as the number of relationships grow. The pattern of values and NULL in the report shows which groups have been formed for a summary aggregate. Explicit information about the groups is provided by the GROUPING function.

W. Use the GROUPING function with CUBE

This example shows how the SELECT statement uses the SUM aggregate, the GROUP BY clause, and the CUBE operator. It also uses the GROUPING function on the two columns listed after the GROUP BY clause.

USE pubs
SELECT pub_name, GROUPING(pub_name),title, GROUPING(title), 
   SUM(qty) AS 'qty'
FROM sales INNER JOIN titles 
   ON sales.title_id = titles.title_id INNER JOIN publishers 
   ON publishers.pub_id = titles.pub_id
GROUP BY pub_name, title
WITH CUBE

The result set has two columns containing 0 and 1 values, which are produced by the GROUPING(pub_name) and GROUPING(title) expressions.

Here is the result set:

pub_name                 title                         qty            
-------------------- --- ------------------------- --- ----------- 
Algodata Infosystems   0 But Is It User Friendly?    0          30 
Algodata Infosystems   0 Cooking with Computers: S   0          25 
Algodata Infosystems   0 Secrets of Silicon Valley   0          50 
Algodata Infosystems   0 Straight Talk About Compu   0          15 
Algodata Infosystems   0 The Busy Executive's Data   0          15 
Algodata Infosystems   0 NULL                        1         135 
Binnet & Hardley       0 Computer Phobic AND Non-P   0          20 
Binnet & Hardley       0 Fifty Years in Buckingham   0          20 
...                                                ...
NULL                   1 The Busy Executive's Data   0          15 
NULL                   1 The Gourmet Microwave       0          40 
NULL                   1 You Can Combat Computer S   0          35 

(36 row(s) affected)
X. Use the ROLLUP operator

This example shows two code examples. This first example retrieves the product name, customer name, and the sum of orders placed and uses the ROLLUP operator.

USE pubs
SELECT product_name, customer_name, SUM(number_of_orders) 
   AS 'Sum orders'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP

Here is the result set:

product_name                   customer_name                  Sum orders 
------------------------------ ------------------------------ ---------- 
Filo Mix                       Eastern Connection             40                   
Filo Mix                       Romero y tomillo               80                   
Filo Mix                       Wilman Kala                    30                   
Filo Mix                       NULL                          150                  
Ikura                          Romero y tomillo               20                   
Ikura                          Wilman Kala                    50                   
Ikura                          NULL                           70                   
Outback Lager                  Eastern Connection             10                   
Outback Lager                  Wilman Kala                    30                   
Outback Lager                  NULL                           40                   
NULL                           NULL                           260                  

(11 row(s) affected)

This second example performs a ROLLUP operation on the company and department columns and totals the number of employees.

The ROLLUP operator produces a summary of aggregates. This is useful when summary information is needed but a full CUBE provides extraneous data or when you have sets within sets. For example, departments within a company are a set within a set.

USE pubs
CREATE TABLE personnel
(
 company_name varchar(20),
 department   varchar(15),
 num_employees int
)

INSERT personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT personnel VALUES ('Piccolo und mehr', 'Payroll', 40)

In this query, the company name, department, and the sum of all employees for the company become part of the result set, in addition to the ROLLUP calculations.

SELECT company_name, department, SUM(num_employees)
FROM personnel
GROUP BY company_name, department WITH ROLLUP

Here is the result set:

company_name         department                  
-------------------- --------------- ----------- 
Du monde entier      Engineering     40          
Du monde entier      Finance         10          
Du monde entier      Marketing       40          
Du monde entier      NULL            90          
Piccolo und mehr     Accounting      20          
Piccolo und mehr     Payroll         40          
Piccolo und mehr     Personnel       30          
Piccolo und mehr     NULL            90          
NULL                 NULL            180         

(9 row(s) affected)
Y. Use the GROUPING function

This example adds three new rows to the cube_examples table. Each of the three records NULL in one or more columns to show only the ROLLUP function produces a value of 1 in the grouping column. In addition, this example modifies the SELECT statement that was used in the earlier example.

USE pubs
-- Add first row with a NULL customer name and 0 orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES ('Ikura', NULL, 0)

-- Add second row with a NULL product and NULL customer with real value 
-- for orders.
INSERT cube_examples (product_name, customer_name, number_of_orders)
   VALUES (NULL, NULL, 50)

-- Add third row with a NULL product, NULL order amount, but a real 
-- customer name.
INSERT cube_examples (product_name, customer_name, number_of_orders)
VALUES (NULL, 'Wilman Kala', NULL)

SELECT product_name AS Prod, customer_name AS Cust, 
   SUM(number_of_orders) AS 'Sum Orders',
   GROUPING(product_name) AS 'Grp prod_name',
   GROUPING(customer_name) AS 'Grp cust_name'
FROM cube_examples
GROUP BY product_name, customer_name
WITH ROLLUP

The GROUPING function can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL, because the column value is NULL and represents the set of all values. The GROUPING function returns 0 when the corresponding column (whether it is NULL or not) did not come from either the CUBE or ROLLUP options as a syntax value. The returned value has a tinyint data type.

Here is the result set:

Prod          Cust               Sum Orders  Grp prod_name Grp cust_name 
------------- ------------------ ----------- ------------- ------------- 
NULL          NULL               50          0             0             
NULL          Wilman Kala        NULL        0             0             
NULL          NULL               50          0             1             
Filo Mix      Eastern Connection 40          0             0             
Filo Mix      Romero y tomillo   80          0             0             
Filo Mix      Wilman Kala        30          0             0             
Filo Mix      NULL               150         0             1             
Ikura         NULL               0           0             0             
Ikura         Romero y tomillo   20          0             0             
Ikura         Wilman Kala        50          0             0             
Ikura         NULL               70          0             1             
Outback Lager Eastern Connection 10          0             0             
Outback Lager Wilman Kala        30          0             0             
Outback Lager NULL               40          0             1             
NULL          NULL               310         1             1             

(15 row(s) affected)
Z. Use SELECT with GROUP BY, an aggregate function, and ROLLUP

This example uses a SELECT query that contains an aggregate function and a GROUP BY clause, which lists pub_name, au_lname, and title, in that order.

USE pubs
SELECT pub_name, au_lname, title, SUM(qty) AS 'SUM'
FROM authors INNER JOIN titleauthor 
   ON authors.au_id = titleauthor.au_id INNER JOIN titles 
   ON titles.title_id = titleauthor.title_id INNER JOIN publishers 
   ON publishers.pub_id = titles.pub_id INNER JOIN sales 
   ON sales.title_id = titles.title_id
GROUP BY pub_name, au_lname, title
WITH ROLLUP

By using the ROLLUP operator, these groupings are created by moving right to left along the list of columns.

pub_name      au_lname      title   SUM(qty)
pub_name      au_lname      NULL      SUM(qty)
pub_name      NULL         NULL      SUM(qty)
NULL         NULL         NULL      SUM(qty)

NULL represents all values for that column.

If you use the SELECT statement without the ROLLUP operator, the statement creates a single grouping. The query returns a sum value for each unique combination of pub_name, au_lname, and title.

pub_name       au_lname       title   SUM(qty)

Compare these examples with the groupings created by using the CUBE operator on the same query.

pub_name      au_lname      title   SUM(qty)
pub_name      au_lname      NULL      SUM(qty)
pub_name      NULL         NULL      SUM(qty)
NULL         NULL         NULL      SUM(qty)
NULL         au_lname      title   SUM(qty)
NULL         au_lname      NULL      SUM(qty)
pub_name      NULL         title   SUM(qty)
NULL         NULL         title   SUM(qty)

The groupings correspond to the information returned in the result set. NULL in the result set represents all values in the column. The ROLLUP operator returns the following data when the columns (pub_name, au_lname, title) are in the order listed in the GROUP BY clause:

Here is the result set:

pub_name          au_lname     title                                SUM
----------------- ------------ ------------------------------------ ---
Algodata Infosys  Bennet       The Busy Executive's Database Guide  15 
Algodata Infosys  Bennet       NULL                                 15 
Algodata Infosys  Carson       NULL                                 30 
Algodata Infosys  Dull         Secrets of Silicon Valley            50 
Algodata Infosys  Dull         NULL                                 50 
...                                                   ...
New Moon Books    White        Prolonged Data Deprivation: Four     15 
New Moon Books    White        NULL                                 15 
New Moon Books    NULL         NULL                                316 
NULL              NULL         NULL                                791 

(49 row(s) affected)

The GROUPING function can be used with the ROLLUP operator or with the CUBE operator. You can apply this function to one of the columns in the select list. The function returns either 1 or 0 depending upon whether the column is grouped by the ROLLUP operator.

a. Use the INDEX optimizer hint

This example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table and the second example forces a table scan by using an index of 0.

-- Use the specifically named INDEX.
USE pubs
SELECT au_lname, au_fname, phone
FROM authors WITH (INDEX(aunmind))
WHERE au_lname = 'Smith'

Here is the result set:

au_lname                               au_fname             phone        
-------------------------------------- -------------------- ---------- 
Smith                                  Meander              913 843-0462 

(1 row(s) affected)

-- Force a table scan by using INDEX = 0.
USE pubs
SELECT emp_id, fname, lname, hire_date
FROM employee (index = 0)
WHERE hire_date > '10/1/1994'
b. Use OPTION and the GROUP hints

This example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.

USE pubs
SELECT a.au_fname, a.au_lname, SUBSTRING(t.title, 1, 15)
FROM authors a INNER JOIN titleauthor ta 
   ON a.au_id = ta.au_id INNER JOIN titles t 
   ON t.title_id = ta.title_id
GROUP BY a.au_lname, a.au_fname, t.title
ORDER BY au_lname ASC, au_fname ASC
OPTION (HASH GROUP, FAST 10)
c. Use the UNION query hint

This example uses the MERGE UNION query hint.

USE pubs
SELECT *
FROM authors a1
OPTION (MERGE UNION)
SELECT *
FROM authors a2
d. Use a simple UNION

The result set in this example includes the contents of the ContactName, CompanyName, City, and Phone columns of both the Customers and SouthAmericanCustomers tables.

USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'SouthAmericanCustomers')
   DROP TABLE SouthAmericanCustomers
GO
-- Create SouthAmericanCustomers table.
SELECT ContactName, CompanyName, City, Phone
INTO SouthAmericanCustomers
FROM Customers
WHERE Country IN ('USA', 'Canada')
GO
-- Here is the simple union.
USE Northwind
SELECT ContactName, CompanyName, City, Phone
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
e. Use SELECT INTO with UNION

In this example, the INTO clause in the first SELECT statement specifies that the table named CustomerResults holds the final result set of the union of the designated columns of the Customers and SouthAmericanCustomers tables.

USE Northwind
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'CustomerResults')
   DROP TABLE CustomerResults
GO
USE Northwind
SELECT ContactName, CompanyName, City, Phone INTO CustomerResults
FROM Customers
WHERE Country IN ('USA', 'Canada')
UNION
SELECT ContactName, CompanyName, City, Phone
FROM SouthAmericanCustomers
ORDER BY CompanyName, ContactName ASC
GO
f. Use UNION of two SELECT statements with ORDER BY

The order of certain parameters used with the UNION clause is important. This example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.

/* INCORRECT */
USE Northwind
GO
SELECT City
FROM Customers
ORDER BY Cities
UNION
SELECT Cities = City 
FROM SouthAmericanCustomers
GO

/* CORRECT */
USE Northwind
GO
SELECT Cities = City
FROM Customers
   UNION
SELECT City 
FROM SouthAmericanCustomers
ORDER BY Cities
GO
g. Use UNION of three SELECT statements showing the effects of ALL and parentheses

These examples use UNION to combine the results of three tables, in which all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.

The final example uses ALL with the first UNION, and parentheses around the second UNION that is not using ALL. The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT through the UNION ALL keywords, which does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.

USE Northwind
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'CustomersOne')
   DROP TABLE CustomersOne
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'CustomersTwo')
   DROP TABLE CustomersTwo
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'CustomersThree')
   DROP TABLE CustomersThree
GO
USE Northwind
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersOne
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersTwo
FROM Customers
WHERE Country = 'Mexico'
GO
SELECT ContactName, CompanyName, City, Phone INTO CustomersThree
FROM Customers
WHERE Country = 'Mexico'
GO
-- Union ALL
SELECT ContactName
FROM CustomersOne
   UNION ALL
SELECT ContactName 
FROM CustomersTwo
   UNION ALL
SELECT ContactName 
FROM CustomersThree
GO

USE Northwind
GO
SELECT ContactName
FROM CustomersOne
   UNION 
SELECT ContactName 
FROM CustomersTwo
   UNION 
SELECT ContactName 
FROM CustomersThree
GO

USE Northwind
GO
SELECT ContactName 
FROM CustomersOne
   UNION ALL
   (
      SELECT ContactName 
      FROM CustomersTwo
         UNION
      SELECT ContactName 
      FROM CustomersThree
   )
GO

See Also

CREATE TRIGGER

CREATE VIEW

DELETE

Distributed Queries

EXECUTE

Expressions

INSERT

LIKE

sp_dboption

Subquery Fundamentals

UNION

UPDATE

Using Variables and Parameters

WHERE