Microsoft SQL Server T-SQL in 10mn ~ Lesson 15. Creating Advanced Joins samples..., Sql Server

Language: Layout:

/*
*/
-- Microsoft TSQL Crash Course
-- http://www.forta.com/books/0672337924/
-- Example table creation scripts
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 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 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 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 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 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
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;
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;
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'											)
;
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;
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		)
;
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/ch15.html

Lesson 15. Creating Advanced Joins

In this lesson, you learn all about additional join types—what they are and how to use them. You also learn how to use table aliases and how to use aggregate functions with joined tables.

Using Table Aliases

Back in Lesson 9, “Creating Calculated Fields,” you learned how to use aliases to refer to retrieved table columns. The syntax to alias a column looks like this:
*/
SELECT 
		RTrim(vend_name) + ' (' + RTrim(vend_country) + ')'       AS vend_title
,		'LS15A' QNM
FROM 
		vendors
ORDER 
BY		vend_name
;
/*
In addition to using aliases for column names and calculated fields, SQL also enables you to alias table names. There are two primary reasons to do this:

To shorten the SQL syntax

To enable multiple uses of the same table within a single SELECT statement

Take a look at the following SELECT statement. It is basically the same statement as an example used in the previous lesson, but it has been modified to use aliases:
*/
SELECT 
		cust_name
,		cust_contact
,		'LS15B - alias example#1' qnm
FROM 
		customers	AS c
, 
		orders		AS o
,	
		orderitems	AS oi
WHERE	1=1
AND		c.cust_id		= o.cust_id  
AND		oi.order_num	= o.order_num  
AND		prod_id			= 'TNT2'
;
/*
Analysis

You’ll notice that the three tables in the FROM clauses all have aliases. customers AS c establishes c as an alias for customers, and so on. This enables you to use the abbreviated c instead of the full text customers. In this example, the table aliases were used only in the WHERE clause, but aliases are not limited to just WHERE. You can use aliases in the SELECT list, the ORDER BY clause, and in any other part of the statement as well.

Note: Execution Time Only

It is worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned back to the client in the results.

Using Different Join Types

So far, you have used only simple joins known as inner joins or equijoins. You’ll now take a look at three additional join types: the self join, the natural join, and the outer join.

Self Joins

As mentioned earlier, one of the primary reasons to use table aliases is to be able to refer to the same table more than once in a single SELECT statement. An example will demonstrate this.

Suppose that a problem is found with a product (item ID DTNTR), and you want to know all the products made by the same vendor so as to determine whether the problem applies to them too. This query requires that you first find out which vendor creates item DTNTR and then find which other products are made by that vendor. The following is one way to approach this problem:

*/
SELECT 
		prod_id
,		prod_name
,		'LS15C - self join example#2 - subquery' qnm
FROM 
		products
WHERE	1=1
AND		vend_id =	(	SELECT 
								vend_id                 
						FROM 
								products                 
						WHERE	1=1
						AND		prod_id = 'DTNTR'
					)
;
/*
Analysis

This first solution uses subqueries. The inner SELECT statement does a simple retrieval to return the vend_id of the vendor that makes item DTNTR. That ID is the one used in the WHERE clause of the outer query, so all items produced by that vendor are retrieved. (You learned all about subqueries in Lesson 13. Refer to that lesson for more information.)

Now look at the same query using a join:
*/
SELECT 
		p1.prod_id
,		p1.prod_name
,		'LS15D - self join example#3 - alias equi-moin' qnm
FROM 
		products AS p1
,		products AS p2
WHERE	1=1
AND		p1.vend_id = p2.vend_id  
AND		p2.prod_id = 'DTNTR'
;
/*
Analysis

The two tables needed in this query are actually the same table, so the products table appears in the FROM clause twice. Although this is perfectly legal, any references to table products would be ambiguous because SQL Server could not know to which instance of the products table you are referring.

To resolve this problem, table aliases are used. The first occurrence of products has an alias of p1, and the second has an alias of p2. Now those aliases can be used as table names. The SELECT statement, for example, uses the p1 prefix to explicitly state the full name of the desired columns. If it did not, SQL Server would return an error because there are two columns named prod_id and prod_name. It cannot know which one you want (even though, in truth, they are one and the same). The WHERE clause first joins the tables (by matching vend_id in p1 to vend_id in p2) and then filters the data by prod_id in the second table to return only the desired data.

Tip: Self Joins Instead of Subqueries

Self joins are often used to replace statements using subqueries that retrieve data from the same table as the outer statement. Although the end result is the same, sometimes these joins execute far more quickly than do subqueries. It is usually worth experimenting with both to determine which performs better.

Natural Joins

Whenever tables are joined, at least one column appears in more than one table (the columns being joined). Standard joins (the inner joins you learned about in the previous lesson) return all data, even multiple occurrences of the same column. A natural join simply eliminates those multiple occurrences so only one of each column is returned.

How does it do this? The answer is, it doesn’t—you do it. A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables. The following is an example:

*/
SELECT 
		c.*
--		
,		'|' as "|"
,		o.order_num
,		o.order_date
,       oi.prod_id
,		oi.quantity
,		OI.item_price
,		'LS15E - Natural Join example' qnm
FROM 
		customers	AS c
, 
		orders		AS o
, 
		orderitems	AS oi
WHERE	1=1
AND		c	.cust_id	= o.cust_id  
AND		oi	.order_num	= o.order_num  
AND			 prod_id	= 'FB'
;
/*
Analysis

In this example, a wildcard is used for the first table only. All other columns are explicitly listed so no duplicate columns are retrieved.

The truth is, every inner join you have created thus far is actually a natural join, and you will probably never even need an inner join that is not a natural join.

Outer Joins

Most joins relate rows in one table with rows in another. But occasionally, you want to include rows that have no related rows. For example, you might use joins to accomplish the following tasks:

Count how many orders each customer placed, including customers who have yet to place an order.

List all products with order quantities, including products not ordered by anyone.

Calculate average sale sizes, taking into account customers who have not yet placed an order.

In each of these examples, the join includes table rows that have no associated rows in the related table. This type of join is called an outer join.

The following SELECT statement is a simple inner join. It retrieves a list of all customers and their orders:

*/
SELECT 
		customers	.cust_id
,		orders		.order_num
,		'LS15F - equi-join customers,orders' qnm
FROM 
		customers 
			INNER JOIN 
				orders 
				ON	1=1
				AND	customers.cust_id = orders.cust_id
;
/*
Outer join syntax is similar. To retrieve a list of all customers, including those who have placed no orders, you can do the following:
*/
SELECT 
		customers	.cust_id
,		orders		.order_num
,		'LS15G - outer join customers,orders' qnm
FROM 
		customers 
			LEFT OUTER JOIN 
				orders 
					ON	1=1
					AND	customers.cust_id = orders.cust_id
;
/*
Analysis

Like the inner join shown in the previous lesson, this SELECT statement uses the keywords OUTER JOIN to specify the join type (instead of specifying it in the WHERE clause). But unlike inner joins, which relate rows in both tables, outer joins also include rows with no related rows. When using OUTER JOIN syntax, you must use the RIGHT or LEFT keyword to specify the table from which to include all rows (RIGHT for the one on the right of OUTER JOIN, and LEFT for the one on the left). The previous example uses LEFT OUTER JOIN to select all the rows from the table on the left in the FROM clause (the customers table). To select all the rows from the table on the right, you use a RIGHT OUTER JOIN, as shown in this example:

*/
SELECT 
		customers	.cust_id
,		orders		.order_num
,		'LS15H - RIGHT outer join customers,orders' qnm
FROM 
		customers 
			RIGHT OUTER JOIN 
				orders 
					ON	1=1
					AND	orders	.cust_id = customers	.cust_id
;
/*
Tip: Outer Join Types

There are two basic forms of outer joins—the left outer join and the right outer join. The only difference between them is the order of the tables they are relating. In other words, a left outer join can be turned into a right outer join simply by reversing the order of the tables in the FROM or WHERE clause. As such, the two types of outer joins can be used interchangeably, and the decision about which one is used is based purely on convenience.

Note: Non-ANSI Outer Joins

In the previous lesson, you learned two ways to write an inner join: using a simplified WHERE clause and using INNER JOIN syntax. In this lesson, you’ve seen the ANSI-style OUTER JOIN syntax, but not a simplified WHERE clause outer join.

The truth is there is a simplified syntax for outer joins using a WHERE clause. I’ll show it to you, but must first tell you to never use it.

Here’s the simplified outer join:

*/
/*
SELECT 
		customers	.	cust_id
,		orders		.	order_num
,		'LS15I - OUTER join simplified syntax' qnm
FROM 
		customers
, 
		orders
WHERE	1=1
AND		customers.cust_id *=orders.cust_id -- not ms sql 2017 compatible
;
*/
/*
The *= instructs SQL Server to retrieve all the rows from the first table (customers, the table nearer the *) and only related rows from the second table (orders, the table nearer the =). Therefore, *= creates a left outer join. Similarly, =* would create a right outer join (because * is on the right).

As stated previously, it is a simpler syntax. So why not use it? This form of syntax is not part of the ANSI standard and as of SQL Server 2005 is no longer supported by default in SQL Server. The only reason I mention it is so that you’ll know what it is if you come across it.

This does not apply to the simplified inner join syntax (shown in the last lesson), which Microsoft has not announced any intention of dropping support for.
*/
/*
There is one other form of outer join worth noting, although you will likely rarely find a use for it. The FULL OUTER JOIN is used to retrieve the related rows from both tables, as well as the unrelated rows from each (these will have NULL values for the unrelated columns in the other table). The syntax for a FULL OUTER JOIN is the same as the previously shown outer joins, obviously substituting RIGHT and LEFT for FULL.

Using Joins with Aggregate Functions

As you learned in Lesson 11, “Summarizing Data,” aggregate functions are used to summarize data. Although all the examples of aggregate functions thus far only summarized data from a single table, these functions can also be used with joins.

To demonstrate this, let’s look at an example. You want to retrieve a list of all customers and the number of orders that each has placed. The following code uses the Count() function to achieve this:

*/
SELECT 
		customers	.	cust_name
,       customers	.	cust_id
,       Count	(	orders	.	order_num
				) 
		AS num_ord
,		'LS15J - equijoin customers,orders' qnm
FROM 
		customers 
			INNER JOIN 
				orders 
					ON	1						= 1
					AND	customers	.	cust_id = orders	.	cust_id
GROUP 
BY		customers	.	cust_name
,       customers	.	cust_id
;
/*
Analysis

This SELECT statement uses INNER JOIN to relate the customers and orders tables to each other. The GROUP BY clause groups the data by customer, and so the function call Count(orders.order_num) counts the number of orders for each customer and returns it as num_ord.

Aggregate functions can be used just as easily with other join types. See the following example:

*/
SELECT 
		customers	.	cust_name
,       customers	.	cust_id
,       Count	(	orders	.	order_num
				) 
		AS num_ord
,		'LS15K - LEFT OUTER JOIN example' qnm
FROM 
		customers 
			LEFT OUTER JOIN 
				orders 
					ON	1=1
					AND	customers	.	cust_id		= orders	.	cust_id
GROUP 
BY		customers	.	cust_name
,       customers	.	cust_id
;
/*
Analysis

This example uses a left outer join to include all customers, even those who have not placed any orders. The results show that customer Mouse House (with no orders) is also included this time.

Note: Null Value Elimination Warning

Depending on the database client you are using, you may have seen the following warning generated by the previous T-SQL statement

Warning: Null value is eliminated by an aggregate or otherSET operation.

This is not an error message; it is just an informational warning, and it is telling you that a row (in this case, Mouse House) should have returned NULL because there are no orders. However, because an aggregate function was used (the Count() function), that NULL was converted to a number (in this case, 0).

Using Joins and Join Conditions

Before wrapping up this two-lesson discussion on joins, it is worthwhile to summarize some key points regarding joins and their use:

Pay careful attention to the type of join being used. More often than not, you’ll want an inner join, but there are often valid uses for outer joins too.

Make sure you use the correct join condition, or you’ll return incorrect data.

Make sure you always provide a join condition, or you’ll end up with the Cartesian product.

You may include multiple tables in a join and even have different join types for each. Although this is legal and often useful, make sure you test each join separately before testing them together. This makes troubleshooting far simpler.

Summary

This lesson was a continuation of the previous lesson on joins. This lesson started by teaching you how and why to use aliases and then continued with a discussion on different join types and the various forms of syntax used with each. You also learned how to use aggregate functions with joins, and some important do’s and don’ts to keep in mind when working with joins.
*/

View schema
Execution time: 0,08 sec, rows selected: 49, rows affected: 55, absolute service time: 0,3 sec

Microsoft SQL Server T-SQL in 10mn ~ Lesson 15. Creating Advanced Join...