?
Solved

How does LEFT JOIN work ?

Posted on 2003-03-16
8
Medium Priority
?
922 Views
Last Modified: 2012-06-21
I have a 20 table database, built around the Applications table. Each Application row will probably have a link to a row in the Organisations table. Each Organisation row might have a link to a row in the Locations table for the registered office, and another for the postal address. Also, each Application row might have a link to a row in the Categories table. And so it goes on...

I sort of understand about LEFT JOINS, but I would like to read some sort of tutorial that descibes in detail how to construct a SELECT statement that will return all data, even when a link is missing. Notice that in the above partial description, the Locations table has to be linked twice - once for the Office location, and once for the Postal address.

I've tried setting the Relationship structure in Access, then having it construct the appropriate SQL, but it quickly complains about ambiguous joins.
0
Comment
Question by:robertlees
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Expert Comment

by:markhoy
ID: 8146319
1. The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

2. Using Outer Joins
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join

Microsoft. SQL Server 2000 uses these SQL-92 keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN


RIGHT OUTER JOIN or RIGHT JOIN


FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.

Using Left Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson).

To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. The following is the query and results of the Transact-SQL left outer join:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname             au_lname                       pub_name          
-------------------- ------------------------------ -----------------
Reginald             Blotchet-Halls                 NULL
Michel               DeFrance                       NULL
Innes                del Castillo                   NULL
Ann                  Dull                           NULL
Marjorie             Green                          NULL
Morningstar          Greene                         NULL
Burt                 Gringlesby                     NULL
Sheryl               Hunter                         NULL
Livia                Karsen                         NULL
Charlene             Locksley                       NULL
Stearns              MacFeather                     NULL
Heather              McBadden                       NULL
Michael              O'Leary                        NULL
Sylvia               Panteley                       NULL
Albert               Ringer                         NULL
Anne                 Ringer                         NULL
Meander              Smith                          NULL
Dean                 Straight                       NULL
Dirk                 Stringer                       NULL
Johnson              White                          NULL
Akiko                Yokomoto                       NULL
Abraham              Bennet                         Algodata Infosystems
Cheryl               Carson                         Algodata Infosystems

(23 row(s) affected)

The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

Using Right Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname             au_lname                 pub_name            
-------------------- ------------------------ --------------------
Abraham              Bennet                   Algodata Infosystems
Cheryl               Carson                   Algodata Infosystems
NULL                 NULL                     Binnet & Hardley
NULL                 NULL                     Five Lakes Publishing
NULL                 NULL                     GGG&G
NULL                 NULL                     Lucerne Publishing
NULL                 NULL                     New Moon Books
NULL                 NULL                     Ramona Publishers
NULL                 NULL                     Scootney Books

(9 row(s) affected)

An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold fewer than 50 copies:

USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
   ON s.title_id = t.title_id
   AND s.qty > 50
ORDER BY s.stor_id ASC

Here is the result set:

stor_id qty    title                                                    
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?                                  
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
            Variations                  
(null) (null) Cooking with Computers: Surreptitious Balance Sheets      
(null) (null) Emotional Security: A New Algorithm                        
(null) (null) Fifty Years in Buckingham Palace Kitchens                  
7066   75     Is Anger the Enemy?                                        
(null) (null) Life Without Fear                                          
(null) (null) Net Etiquette                                              
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
            Mediterranean                  
(null) (null) Prolonged Data Deprivation: Four Case Studies              
(null) (null) Secrets of Silicon Valley                                  
(null) (null) Silicon Valley Gastronomic Treats                          
(null) (null) Straight Talk About Computers                              
(null) (null) Sushi, Anyone?                                            
(null) (null) The Busy Executive's Database Guide                        
(null) (null) The Gourmet Microwave                                      
(null) (null) The Psychology of Computer Cooking                        
(null) (null) You Can Combat Computer Stress!                            

(18 row(s) affected)

For more information about predicates, see WHERE.

Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft. SQL Server 2000 provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. The following is the query and results of the Transact-SQL full outer join:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname             au_lname                     pub_name            
-------------------- ---------------------------- --------------------
Reginald             Blotchet-Halls               NULL
Michel               DeFrance                     NULL
Innes                del Castillo                 NULL
Ann                  Dull                         NULL
Marjorie             Green                        NULL
Morningstar          Greene                       NULL
Burt                 Gringlesby                   NULL
Sheryl               Hunter                       NULL
Livia                Karsen                       NULL
Charlene             Locksley                     NULL
Stearns              MacFeather                   NULL
Heather              McBadden                     NULL
Michael              O'Leary                      NULL
Sylvia               Panteley                     NULL
Albert               Ringer                       NULL
Anne                 Ringer                       NULL
Meander              Smith                        NULL
Dean                 Straight                     NULL
Dirk                 Stringer                     NULL
Johnson              White                        NULL
Akiko                Yokomoto                     NULL
Abraham              Bennet                       Algodata Infosystems
Cheryl               Carson                       Algodata Infosystems
NULL                 NULL                         Binnet & Hardley
NULL                 NULL                         Five Lakes Publishing
NULL                 NULL                         GGG&G
NULL                 NULL                         Lucerne Publishing
NULL                 NULL                         New Moon Books
NULL                 NULL                         Ramona Publishers
NULL                 NULL                         Scootney Books

(30 row(s) affected)

3. SELECT EXAMPLES:


 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:

Quantity of each title that each publisher has sold


Quantity of each title sold


Quantity of titles sold by each publisher


Total number of titles sold by all publishers
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:

Quantity of each title that each publisher has sold for each author


Quantity of all titles each publisher has sold for each author


Quantity of all titles each publisher has sold


Total quantity of all titles sold by all publishers for all authors


Quantity of each title sold by all publishers for each author


Quantity of all titles sold by all publishers for each author


Quantity of each title sold by each publisher for all authors


Quantity of each title sold by all publishers for each author


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:

Quantity of each title that each publisher has sold for each author


Quantity of all titles each publisher has sold for each author


Quantity of all titles each publisher has sold


Total quantity of all titles sold by all publishers for all authors
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

4. An inner join includes only those rows of the table on each side of the expression that has matching rows in the other table.

An outer join may be either a LEFT OUTER or a RIGHT OUTER join. An outer join includes all rows of the table on the LEFT (RIGHT) of the expression whether or not there are matching rows in the other table.

5. Left join: take all the records from your first table and if exist a record in table2, links the values to that record (otherwise null-values)
fe:
select * from table1 left join table2 on ....

right join: take all the records from your second table and if exist a record in table1, links the values to that record (otherwise null-values)
fe:
select * from table1 right join table2 on ....

HTH!
0
 
LVL 1

Author Comment

by:robertlees
ID: 8149290
Thanks, markhoy for that explanation of the SELECT. Unfortunately the section dealing with OUTER joins did not go far enough. It just covered the straight forward information, which I already knew. I get into trouble when multiple joins are required - ie, more than 2 tables.

This is the way I think it works.

Each join looks like this: 'Applications a LEFT JOIN Organisations o ON a.OrganisationID = o.ID'

The result of each such join is a 'virtual' table, which can be joined with another table - real or 'virtual'.

Therefore, if an Organisation might have a link to a row in the Locations table, the 3 tables would be joined thus:
(Applications a LEFT JOIN Organisations o ON a.OrganisationID = o.ID) LEFT JOIN Locations l ON o.LocationID = l.ID

Then if a Location might have a link to a row in the States table, the 4 tables would be linked using:
((Applications a LEFT JOIN Organisations o ON a.OrganisationID = o.ID) LEFT JOIN Locations l ON o.LocationID = l.ID) LEFT JOIN States s ON l.StateID = s.ID

Am I right so far ?

Anyway, I pasted such a command into SQL Server Enterprise Manager, Open Table, Query. It accepted the syntax, and drew a relationship diagram.

However when I put the same statement into my VB program which, for the time being, uses a MS-Access-2000 database, it was rejected saying there was a syntax error in the FROM clause.

Are there any subtle differences in the syntax between MS-Access and SQL Server regarding OUTER JOINs ?

Thanks
0
 
LVL 9

Accepted Solution

by:
xenon_je earned 400 total points
ID: 8150440
I didn't had the patience to read all what markhoy wrote above, so if I write something that he wrote, I appologize.
Here is some information which may be usefull for you:

an outer join (be it left, right or full) has the concept:
-'I want all the rows from table A and if the information from table B is not available for some rows than put nulls'(this is the case for left join)

EG:
 A.name A.phone   B.creditnumber
 A.name A.phone   null

An inner join says that it wants only those that has in both tables the join condition
For the previous example the result would have been just the first row.

Now if you have many joins, than the joins added should not conflict with the previous ones.

What this means? If you have an puter join of tables A and B as described above, and then you try to put an inner join between A and a 3rd table C than of course that you will receive an error , because you can obtain all rows from A (as you requested by the outer join), but in the same time eliminate those rows from A that have no corespondence in table C.

I think something like this happened to you.
All you have to do is to try to 'really' understand what joins are (the description in the help is more than enough), and the fact that there are more joins should not bother you.

Good luck
        xenon
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 18

Expert Comment

by:BigSchmuh
ID: 8150839
Your LEFT JOIN troubles surely comes from a basic syntax difference between SqlServer and MSAccess.
You may try to use the graphic query designer of MS-ACCESS to create a basic query with a left join (and a aliased table) and see what results as sql text.
==> You will probably conclude that ALIASing table as a special syntax in MSAccess and won't have any trouble to correct your sql.

Good luck
0
 
LVL 1

Author Comment

by:robertlees
ID: 8207608
Thanks to those who responded. In the end, I upsized from Access to SQL Server, then my statement worked. I had tried build the Relationships in Access, then get it to generate the appropriate SELECT, but it came up with an error saying something like 'Ambiguous joins'. So I built the statement by hand - a very laborious task - and it worked in SQL Server but failed in Access.

0
 

Expert Comment

by:CleanupPing
ID: 9275831
robertlees:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183316
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8150440

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question