Microsoft SQL Server T-SQL in 10 mn ~ Lesson 13 Subqueries vers. #5, Sql Server

Language: Layout:

/*
Microsoft TSQL Crash Course
http://www.forta.com/books/0672337924/
Example table creation scripts
*/
/*
Create customers table
*/
CREATE TABLE customers
( cust_id      INT        NOT NULL IDENTITY(1,1)
, cust_name    NCHAR(50)  NOT NULL 
, cust_address NCHAR(50)  NULL 
, cust_city    NCHAR(50)  NULL 
, cust_state   NCHAR(5)   NULL 
, cust_zip     NCHAR(10)  NULL 
, cust_country NCHAR(50)  NULL 
, cust_contact NCHAR(50)  NULL 
, cust_email   NCHAR(255) NULL
);
ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (cust_id);
/*
Create orderitems table
*/
CREATE TABLE orderitems
(order_num  INT       NOT NULL 
,order_item INT       NOT NULL 
,prod_id    NCHAR(10) NOT NULL 
,quantity   INT       NOT NULL 
,item_price MONEY     NOT NULL 
);
ALTER TABLE orderitems
ADD CONSTRAINT pk_orderitems PRIMARY KEY (order_num, order_item);
/*
Create orders table
*/
CREATE TABLE orders
(order_num  INT      NOT NULL IDENTITY(1,1)
,order_date DATETIME NOT NULL 
,cust_id    INT      NOT NULL
);
ALTER TABLE orders
ADD CONSTRAINT pk_orders PRIMARY KEY (order_num);
/*
Create products table
*/
CREATE TABLE products
(prod_id    NCHAR(10)  NOT NULL
,vend_id    INT        NOT NULL 
,prod_name  NCHAR(255) NOT NULL 
,prod_price MONEY      NOT NULL 
,prod_desc  NTEXT      NULL
);
ALTER TABLE products
ADD CONSTRAINT pk_products PRIMARY KEY (prod_id);
/*
Create vendors table
*/
CREATE TABLE vendors
(vend_id      INT       NOT NULL identity(1,1)
,vend_name    NCHAR(50) NOT NULL 
,vend_address NCHAR(50) NULL 
,vend_city    NCHAR(50) NULL 
,vend_state   NCHAR(5)  NULL 
,vend_zip     NCHAR(10) NULL 
,vend_country NCHAR(50) NULL
);
ALTER TABLE vendors
ADD CONSTRAINT pk_vendors PRIMARY KEY (vend_id);
/*
Create productnotes table
*/
CREATE TABLE productnotes
(note_id    INT            NOT NULL IDENTITY(1,1)
,prod_id    NCHAR(10)      NOT NULL
,note_date  DATETIME       NOT NULL
,note_text  NTEXT          NULL
);
ALTER TABLE productnotes
ADD CONSTRAINT pk_productnotes PRIMARY KEY (note_id);
/*
Define foreign keys
*/
ALTER TABLE orderitems   ADD CONSTRAINT fk_orderitems_orders     FOREIGN KEY (order_num) REFERENCES orders    (order_num);
ALTER TABLE orderitems   ADD CONSTRAINT fk_orderitems_products   FOREIGN KEY (prod_id)   REFERENCES products  (prod_id);
ALTER TABLE orders       ADD CONSTRAINT fk_orders_customers      FOREIGN KEY (cust_id)   REFERENCES customers (cust_id);
ALTER TABLE products     ADD CONSTRAINT fk_products_vendors      FOREIGN KEY (vend_id)   REFERENCES vendors   (vend_id);
ALTER TABLE productnotes ADD CONSTRAINT fk_productnotes_products FOREIGN KEY (prod_id)   REFERENCES products  (prod_id);
/*
Example table population scripts
*/
/* Populate customers table */
SET IDENTITY_INSERT customers ON;
INSERT INTO			customers
 (cust_id	, cust_name			, cust_address			, cust_city	, cust_state, cust_zip	, cust_country	, cust_contact	,	cust_email)
VALUES
 (10001		, 'Coyote Inc.'		, '200 Maple Lane'		, 'Detroit'	, 'MI'		, '44444'	, 'USA'			, 'Y Lee'		,	'ylee@coyote.com'		)
,(10002		, 'Mouse House'		, '333 Fromage Lane'	, 'Columbus', 'OH'		, '43333'	, 'USA'			, 'Jerry Mouse'	,	null					)
,(10003		, 'Wascals'			, '1 Sunny Place'		, 'Muncie'	, 'IN'		, '42222'	, 'USA'			, 'Jim Jones'	,	'rabbit@wascally.com'	)
,(10004		, 'Yosemite Place'	, '829 Riverside Drive'	, 'Phoenix'	, 'AZ'		, '88888'	, 'USA'			, 'Y Sam'		,	'sam@yosemite.com'		)
,(10005		, 'E Fudd'			, '4545 53rd Street'	, 'Chicago'	, 'IL'		, '54545'	, 'USA'			, 'E Fudd'		,	null					)
;
SET IDENTITY_INSERT customers OFF;
/* Populate vendors table */
SET IDENTITY_INSERT vendors ON;
INSERT INTO			vendors
 (vend_id	, vend_name			, vend_address		, vend_city		, vend_state, vend_zip	, vend_country	)
VALUES
 (1001		,'Anvils R Us'		,'123 Main Street'	,'Southfield'	,'MI'		,'48075'	, 'USA'			)
,(1002		,'LT Supplies'		,'500 Park Street'	,'Anytown'		,'OH'		,'44333'	, 'USA'			)
,(1003		,'ACME'				,'555 High Street'	,'Los Angeles'	,'CA'		,'90046'	, 'USA'			)
,(1004		,'Furball Inc.'		,'1000 5th Avenue'	,'New York'		,'NY'		,'11111'	, 'USA'			)
,(1005		,'Jet Set'			,'42 Galaxy Road'	,'London'		, NULL		,'N16 6PS'	, 'England'		)
,(1006		,'Jouets Et Ours'	,'1 Rue Amusement'	,'Paris'		, NULL		,'45678'	, 'France'		)
;
SET IDENTITY_INSERT vendors OFF;
/* Populate products table */
INSERT INTO			products
(prod_id	, vend_id	, prod_name			, prod_price, prod_desc															)
VALUES
 ('ANV01'	, 1001		, '.5 ton anvil'	, 5.99		, '.5 ton anvil, black, complete with handy hook'					)
,('ANV02'	, 1001		, '1 ton anvil'		, 9.99		, '1 ton anvil, black, complete with handy hook and carrying case'	)
,('ANV03'	, 1001		, '2 ton anvil'		, 14.99		, '2 ton anvil, black, complete with handy hook and carrying case'	)
,('OL1'		, 1002		, 'Oil can'			, 8.99		, 'Oil can, red'													)
,('FU1'		, 1002		, 'Fuses'			, 3.42		, '1 dozen, extra long'												)
,('SLING'	, 1003		, 'Sling'			, 4.49		, 'Sling, one size fits all'										)
,('TNT1'	, 1003		, 'TNT (1 stick)'	, 2.50		, 'TNT, red, single stick'											)
,('TNT2'	, 1003		, 'TNT (5 sticks)'	, 10		, 'TNT, red, pack of 10 sticks'										)
,('FB'		, 1003		, 'Bird seed'		, 10		, 'Large bag (suitable for road runners)'							)
,('FC'		, 1003		, 'Carrots'			, 2.50		, 'Carrots (rabbit hunting season only)'							)
,('SAFE'	, 1003		, 'Safe'			, 50		, 'Safe with combination lock'										)
,('DTNTR'	, 1003		, 'Detonator'		, 13		, 'Detonator (plunger powered), fuses not included'					)
,('JP1000'	, 1005		, 'JetPack 1000'	, 35		, 'JetPack 1000, intended for single use'							)
,('JP2000'	, 1005		, 'JetPack 2000'	, 55		, 'JetPack 2000, multi-use'											)
;
/* Populate orders table */
SET IDENTITY_INSERT orders ON;
INSERT INTO			orders
 (order_num	, order_date	, cust_id)
VALUES
 (20005		, '2016-09-01'	, 10001)
,(20006		, '2016-09-12'	, 10003)
,(20007		, '2016-09-30'	, 10004)
,(20008		, '2016-10-03'	, 10005)
,(20009		, '2016-10-08'	, 10001)
;
SET IDENTITY_INSERT orders OFF;
/* Populate orderitems table */
INSERT INTO orderitems
(order_num	, order_item, prod_id	, quantity	, item_price)
VALUES
 (20005		, 1			, 'ANV01'	, 10		, 5.99		)
,(20005		, 2			, 'ANV02'	, 3			, 9.99		)
,(20005		, 3			, 'TNT2'	, 5			, 10		)
,(20005		, 4			, 'FB'		, 1			, 10		)
,(20006		, 1			, 'JP2000'	, 1			, 55		)
,(20007		, 1			, 'TNT2'	, 100		, 10		)
,(20008		, 1			, 'FC'		, 50		, 2.50		)
,(20009		, 1			, 'FB'		, 1			, 10		)
,(20009		, 2			, 'OL1'		, 1			, 8.99		)
,(20009		, 3			, 'SLING'	, 1			, 4.49		)
,(20009		, 4			, 'ANV03'	, 1			, 14.99		)
;
/* Populate productnotes table */
SET IDENTITY_INSERT productnotes ON;
INSERT INTO			productnotes
 (note_id	, prod_id	, note_date		, note_text)
VALUES
 (101		, 'TNT2'	, '2016-08-17'	,
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'														)
,(102		, 'OL1'		, '2016-08-18'	,
'Can shipped full, refills not available.
Need to order new can if refill needed.'											)
,(103		, 'SAFE'	, '2016-08-18'	,
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'	)
,(104		, 'FC'		, '2016-08-19'	,
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'		)
,(105		, 'TNT2'	, '2016-08-20'	,
'Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.')
,(106		, 'TNT2'	, '2016-08-22'	,
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'	)
,(107		, 'SAFE'	, '2016-08-23'	,
'Please note that no returns will be accepted if safe opened using explosives.'		)
,(108		, 'ANV01'	, '2016-08-25'	,
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.')
,(109		, 'ANV03'	, '2016-09-01'	,
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.')
,(110		, 'FC'		, '2016-09-01'	,
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.')
,(111		, 'SLING'	, '2016-09-02'	,
'Shipped unassembled, requires common tools (including oversized hammer).'			)
,(112		, 'SAFE'	, '2016-09-02'	,
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'				)
,(113		, 'ANV01'	, '2016-09-05'	,
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.')
,(114		, 'SAFE'	, '2016-09-07'	,
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.')
;
SET IDENTITY_INSERT productnotes OFF;
--	https://www.safaribooksonline.com/library/view/sams-teach-yourself/9780134649160/ch13.html
/*
Lesson 13. Working with Subqueries

In this lesson, you learn what subqueries are and how to use them.

Understanding Subqueries

SELECT statements are SQL queries. All the SELECT statements you have seen thus far are 
simple queries: single statements retrieving data from individual database tables.

New Term: Query

Any SQL statement. However, this term is generally used to refer to SELECT statements.

SQL also enables you to create subqueries—queries that are embedded into other queries. 
Why would you want to do this? The best way to understand this concept is to look at a 
couple examples.

Filtering by Subquery

The database tables used in all the lessons in this book are relational tables. (See Appendix B, 
“The Example Tables,” for a description of each of the tables and their relationships.) Order 
data is stored in two tables. The orders table stores a single row for each order containing 
an order number, customer ID, and order date. The individual order items are stored in the 
related orderitems table. The orders table does not store customer information. It only 
stores a customer ID. The actual customer information is stored in the customers table.

Now suppose you wanted a list of all the customers who ordered item TNT2. What would 
you have to do to retrieve this information? Here are the steps to accomplish this:

1. Retrieve the order numbers of all orders containing item TNT2.

2. Retrieve the customer ID of all the customers who have orders listed in the order numbers 
returned in the previous step.

3. Retrieve the customer information for all the customer IDs returned in the previous step.

Each of these steps can be executed as a separate query. By doing so, you use the results 
returned by one SELECT statement to populate the WHERE clause of the next SELECT 
statement.

However, you can also use subqueries to combine all three queries into one single statement.

The first SELECT statement should be self-explanatory by now. It retrieves the order_num 
column for all order items with a prod_id of TNT2. The output lists the two orders 
containing this item:
*/
SELECT 
		order_num
--		added for clarity...m.t.bee
,		prod_id 
,       'LS13A' QueryName
FROM   
		orderitems 
WHERE	1=1
AND		prod_id = 'TNT2'
;  
/*
The next step is to retrieve the customer IDs associated with orders 20005 and 20007. Using 
the IN clause, described in Lesson 7, “Advanced Data Filtering,” you can create a SELECT 
statement as follows:
*/
SELECT 
		cust_id 
--		Added for clarity...
-- ,		prod_id
,       'LS13B' QueryName
FROM   
		orders 
WHERE	1=1
AND		order_num IN ( 0
					 , 20005
					 , 20007 
					 , 0)
;  
/*
Now, combine the two queries by turning the first (the one that returned the order numbers) 
into a subquery. Look at the following SELECT statement:
*/
SELECT 
		cust_id
,       'LS13C' QueryName
FROM 
		orders
WHERE	1=1
AND		order_num IN	(
						SELECT 
								order_num                    
						FROM 
								orderitems                    
						WHERE	1=1
						AND		prod_id = 'TNT2'
						)
;
/*
Subqueries are always processed starting with the innermost SELECT statement and working 
outward. When the preceding SELECT statement is processed, SQL Server actually performs 
two operations.

First, it runs the subquery:
*/	
SELECT order_num,'LS13D' QueryName FROM orderitems WHERE prod_id = 'TNT2';
/*
That query returns the two order numbers, 20005 and 20007. Those two values are then 
passed to the WHERE clause of the outer query in the comma-delimited format required by the IN 
operator. The outer query now becomes this:
*/
SELECT cust_id,'LS13E' QueryName FROM orders WHERE order_num IN (20005, 20007);
/*
As you can see, the output is correct and exactly the same as the output returned by the 
previous hard-coded WHERE clause.

Tip: Formatting Your SQL

SELECT statements containing subqueries can be difficult to read and debug, 
especially as they grow in complexity. Breaking up the queries over multiple lines 
and indenting the lines appropriately, as shown here, can greatly simplify working 
with subqueries.

You now have the IDs of all the customers who ordered item TNT2. The next step is to 
retrieve the customer information for each of those customer IDs. Here is the SQL statement 
to retrieve the two columns:
*/
SELECT 
		cust_name
,		cust_contact
,       'LS13F' QueryName
FROM 
		customers
WHERE	1=1
AND		cust_id IN	(	0
					,	10001
					,	10004
					,	0
					)
;
/*
Instead of hard-coding those customer IDs, you can turn this WHERE clause into yet another subquery:
*/
SELECT 
		cust_name
,		cust_contact
,       'LS13G' QueryName
FROM 
		customers
WHERE	1=1
AND		cust_id IN	(	
						SELECT 
								cust_id
						FROM	
								orders
						WHERE	1=1
						AND		order_num IN	(	
													SELECT 
															order_num
													FROM 
															orderitems
													WHERE	1=1
													AND		prod_id = 'TNT2'
												)
					)
;
/*
Analysis

To execute this SELECT statement, SQL Server actually had to perform three SELECT 
statements. The innermost subquery returned a list of order numbers that was then used as 
the WHERE clause for the subquery above it. That subquery returned a list of customer IDs 
that was used as the WHERE clause for the top-level query. The top-level query actually 
returned the desired data.

As you can see, using subqueries in a WHERE clause enables you to write extremely powerful 
and flexible SQL statements. There is no limit imposed on the number of subqueries that can 
be nested, although in practice you will find that performance tells you when you are nesting 
too deeply.

Caution: Single Column Only

With the notable exception of subqueries used in conjunction with EXISTS (as will 
be shown shortly), subquery SELECT statements can retrieve only a single column, 
and attempting to retrieve multiple columns returns an error.

Although generally used in conjunction with the IN operator, subqueries can also be used to 
test for equality (using =), non-equality (using <>), and so on.

Caution: Subqueries and Performance

The code shown here works, and it achieves the desired result. However, using 
subqueries is not always the most efficient way to perform this type of data 
retrieval, although it might be. More on this is in Lesson 14, “Joining Tables,” where 
you will revisit this same example

Using Subqueries as Calculated Fields

Another way to use subqueries is in creating calculated fields. Suppose you want to display 
the total number of orders placed by every customer in your customers table. Orders are 
stored in the orders table along with the appropriate customer ID.

1. Retrieve the list of customers from the customers table.

2. For each customer retrieved, count the number of associated orders in the orders table.

As you learned in the previous two lessons, you can use SELECT Count(*) to count rows in 
a table, and by providing a WHERE clause to filter a specific customer ID, you can count just 
that customer’s orders. For example, the following code counts the number of orders placed 
by customer 10001:
*/
SELECT 
		Count(*) AS orders
,       'LS13H' QueryName
FROM	
		orders
WHERE	1=1
AND		cust_id = 10001
;
/*
To perform that Count(*) calculation for each customer, use Count(*) as a subquery. 
Look at the following code:
*/
SELECT 
		cust_name
,       cust_state
,       (
			SELECT 
					Count(*)       
			FROM 
					orders       
			WHERE	1=1
			AND		orders.cust_id = customers.cust_id
		) 
		AS orders
,       'ls13I' QueryName
FROM 
		customers
ORDER 
BY		cust_name
;
/*
Analysis

This SELECT statement returns three columns for every customer in the customers table: 
cust_name, cust_state, and orders. orders is a calculated field that is set by a 
subquery provided in parentheses. That subquery is executed once for every customer 
retrieved. In this example, the subquery is executed five times because five customers were 
retrieved.

The WHERE clause in this subquery is a little different from the WHERE clauses used previously 
because it uses fully qualified column names (first mentioned in Lesson 4, “Retrieving Data”). 
The following clause tells SQL to compare the cust_id in the orders table to the one 
currently being retrieved from the customers table:

WHERE orders.cust_id = customers.cust_id

New Term: Correlated Subquery

A subquery that refers to the outer query.

This type of subquery is called a correlated subquery. This syntax—the table name and the 
column name separated by a period—must be used whenever there is possible ambiguity 
about column names. Why? Well, let’s look at what happens if fully qualified column names 
are not used:
*/
SELECT 
		cust_name
,       cust_state
,       (SELECT 
				Count(*)       
		 FROM  
				orders       
		 WHERE	1=1
		 AND	cust_id = cust_id) AS orders
,       'LS13J' QueryName
FROM	customers
ORDER 
BY		cust_name
;
/*
Analysis

Obviously the returned results are incorrect (compare them to the previous results), but why 
did this happen? There are two cust_id columns—one in customers and one in 
orders—and those two columns need to be compared to correctly match orders with their 
appropriate customers. Without fully qualifying the column names, SQL Server assumes you 
are comparing the cust_id in the orders table to itself. As such, the statement

SELECT Count(*) FROM orders WHERE cust_id = cust_id;

always returns the total number of orders in the orders table (because SQL Server checks to 
see that every order’s cust_id matches itself, which it always does, of course).

Although subqueries are extremely useful in constructing this type of SELECT statement, care 
must be taken to properly qualify ambiguous column names.

Note: Always More Than One Solution

As explained earlier in this lesson, although the sample code shown here works, it 
is often not the most efficient way to perform this type of data retrieval. You will 
revisit this example in a later lesson

Checking for Existence with Subqueries

Another use for subqueries is in conjunction with the EXISTS predicate. EXISTS, when used 
in a WHERE clause, looks at the results returned by a subquery—not at specific columns of 
data, but rather at the number of rows. If the subquery returns rows, the EXISTS test is true 
and the WHERE clause matches. However, if no rows are returned, the EXISTS test is false, 
and the WHERE clause does not match.

Look at this SELECT statement:
*/
SELECT 
		cust_id
,		cust_name
,       'LS13K' QueryName
FROM 
		customers
WHERE	1=1
AND		cust_id IN	(
						SELECT 
								cust_id
						--		To test...
						--,		ORDER_DATE	-- comment out when done testing...                  
						FROM 
								orders                  
						WHERE	1=1
						AND		DateDiff	(	month, 
												order_date,                                 
												'2016-09-01'
											) = 0                   
						AND		customers.cust_id = orders.cust_id -- uncomment when done testing
					)
;
/*
Analysis

This SELECT statement retrieves the customer name and ID for any customers who made 
orders in the month of September 2016. Like the examples shown earlier in this lesson, the 
WHERE clause uses IN and a subquery to first select the IDs of customers who made orders 
the specified month, and then uses the results of that subquery to select just the desired 
customers from the customers table.

Now let’s look at another SELECT statement that returns the exact same output:
*/
SELECT 
		cust_id
,		cust_name
,       'LS13L' QueryName
FROM 
		customers
WHERE	1=1
AND		EXISTS	(	SELECT 
							1 AS DUMMY -- *  --            
					FROM 
							orders              
					WHERE	1=1
					AND		DateDiff	(	month
										,	order_date
										,	'2016-09-01'
										) = 0               
					AND		customers.cust_id = orders.cust_id
				)
;
/*
Analysis

This WHERE clause uses EXISTS instead of IN. The subquery is much the same as the one 
used with IN, except that this one also matches the cust_id columns in both the 
customers table and the orders table so that just the customers with orders in September 
2016 are retrieved. You’ll notice that the subquery uses SELECT *, which is usually not 
allowed in subqueries, although in truth it would make no difference what columns were 
selected because it is not the returned data that is being used to filter customers, but the 
existence of any matching data.

So, which to use, IN or EXISTS? For the most part you can use either; both let you use 
subqueries to filter data, and both can be negated using NOT to find rows that don’t match 
(perhaps to find all the customers who did not order in a specific month). The biggest 
practical difference between the two is performance. Sometimes statements using EXISTS 
can be processed quicker than those using IN, which is why it is often best to experiment with 
both options (as well as a third option, using joins, as you will see in the next lesson).

Note: Build Queries with Subqueries Incrementally

Testing and debugging queries with subqueries can be tricky, particularly as these 
statements grow in complexity. The safest way to build (and test) queries with 
subqueries is to write the T-SQL code incrementally, in much the same way as 
SQL Server processes subqueries. Build and test the innermost query first. Then 
build and test the outer query with hard-coded data, and only after you have 
verified that it is working embed the subquery. Then test it again. Keep repeating 
these steps for each additional query. This will take just a little longer to construct 
your queries, but doing so saves you lots of time later (when you try to figure out 
why queries are not working) and significantly increases the likelihood of them 
working the first time.

Summary

In this lesson, you learned what subqueries are and how to use them. The most common uses 
for subqueries are in WHERE clauses, with IN operators, and for populating calculated 
columns. You saw examples of all these types of operations.
*/

View schema
Execution time: 0,14 sec, rows selected: 31, rows affected: 55, absolute service time: 0,33 sec