Microsoft SQL Server in 10 mn - Lesson 14 Joining Tables 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/ch14.html

Lesson 14. Joining Tables

tables used: customers, orders, orderitems, products, vendors

In this lesson, you learn what joins are, why they are used, and how to create SELECT statements us-
ing them.

Understanding Joins

One of SQL’s most powerful features is the capability to join tables on the fly within data-retrieval queries. Joins are one of the most important operations you can perform using SQL SELECT, and a good understanding of joins and join syntax is an extremely important part of learning SQL.

Before you can effectively use joins, you must understand relational tables and the basics of relational database design. What follows is by no means a complete coverage of the subject, but it should be enough to get you up and running.

Understanding Relational Tables

Suppose you had a database table containing a product catalog, with each catalog item in its own row. The kind of information you would store with each item would include a product description and price, along with vendor information about the company that creates the product.

Now suppose you had multiple catalog items created by the same vendor. Where would you store the vendor information (things such as vendor name, address, and contact information)? You wouldn’t want to store that data along with the products for several reasons:

Image Because the vendor information is the same for each product that vendor produces, repeating the information for each product is a waste of time and storage space.

Image If vendor information changes (for example, if the vendor moves or the area code changes), you would need to update every occurrence of the vendor information.

When data is repeated (that is, the vendor information is used with each product), there is a high likelihood that the data will not be entered exactly the same way each time. Inconsistent data is extremely difficult to use in reporting.

The key here is that having multiple occurrences of the same data is never a good thing, and that principle is the basis for relational database design. Relational tables are designed so information is split into multiple tables, one for each datatype. The tables are related to each other through common values (and thus the relational in relational design).

In our example, you can create two tables—one for vendor information and one for product information. The vendors table contains all the vendor information, one table row per vendor, along with a unique identifier for each vendor. This value, called a primary key, can be a vendor ID or any other unique value. (Primary keys were first mentioned in Lesson 1, “Understanding SQL.”)

The products table stores only product information, with no vendor-specific information other than the vendor ID (the vendors table’s primary key). This key, called a foreign key, relates the vendors table to the products table, and using this vendor ID enables you to use the vendors table to find the details about the appropriate vendor.

New Term: Foreign Key

A column in one table that contains the primary key values from another table, thus defining the relationships between tables.

What does this do for you? Well, consider the following:

Vendor information is never repeated, so time and space are not wasted.

If vendor information changes, you can update a single record in the vendors table. Data in related tables does not change.

Because no data is repeated, the data used is obviously consistent, making data reporting and manipulation much simpler.

The bottom line is that relational data can be stored efficiently and manipulated easily. Because of this, relational databases scale far better than nonrelational databases.

New Term: Scale

Able to handle an increasing load without failing. A well-designed database or application is said to scale well.

Why Use Joins?

As just explained, breaking data into multiple tables enables more efficient storage, easier manipulation, and greater scalability. But these benefits come with a price.

If data is stored in multiple tables, how can you retrieve that data with a single SELECT statement?

The answer is to use a join. Simply put, a join is a mechanism used to associate tables within a SELECT statement (and thus the name join). Using a special syntax, you can join multiple tables so a single set of output is returned, and the join associates the correct rows in each table on the fly.

Note: Maintaining Referential Integrity

It is important to understand that a join is not a physical entity—in other words, it does not exist in the actual database tables. A join is created by SQL Server as needed, and it persists for the duration of the query execution.

When using relational tables, it is important that you only insert valid data into relational columns. Going back to the example, if products were stored in the products table with an invalid vendor ID (one not present in the vendors table), those products would be inaccessible because they would not be related to any vendor.

To prevent this from occurring, SQL Server can be instructed to only allow valid values (ones present in the vendors table) in the vendor ID column in the products table. This is known as maintaining referential integrity, and it is achieved by specifying the primary and foreign keys as part of the table definitions (as will be explained in Lesson 20, “Creating and Manipulating Tables”).

Creating a Join

Creating a join is very simple. You must specify all the tables to be included and how they are related to each other. Look at the following example:

*/
SELECT 
		vend_name
,		prod_name
,		prod_price
,		'LS14A' QNM
FROM 
		vendors
,		products
WHERE	1=1
AND		vendors.vend_id = products.vend_id
ORDER 
BY		vend_name
,		prod_name
;
/*
Analysis

In the preceding code, the SELECT statement starts in the same way as all the statements you’ve looked at thus far, by specifying the columns to be retrieved. The big difference here is that two of the specified columns (prod_name and prod_price) are in one table, whereas the other (vend_name) is in another table.

Now look at the FROM clause. Unlike all the prior SELECT statements, this one has two tables listed in the FROM clause: vendors and products. These are the names of the two tables that are being joined in this SELECT statement. The tables are correctly joined with a WHERE clause that instructs SQL Server to match vend_id in the vendors table with vend_id in the products table.

You’ll notice that the columns are specified as vendors.vend_id and products.vend_id. This fully qualified column name is required here because if you just specify vend_id, SQL Server cannot tell which vend_id columns you are referring to (because there are two of them, one in each table).

Caution: Fully Qualifying Column Names

You must use the fully qualified column name (table and column separated by a period) whenever there is possible ambiguity about to which column you are referring. SQL Server returns an error message if you refer to an ambiguous column name without fully qualifying it with a table name.

The Importance of the WHERE Clause

It might seem strange to use a WHERE clause to set the join relationship, but actually there is a very good reason for this. Remember, when tables are joined in a SELECT statement, that relationship is constructed on the fly. Nothing in the database table definitions can instruct SQL Server how to join the tables. You have to do that yourself. When you join two tables, what you are actually doing is pairing every row in the first table with every row in the second table. The WHERE clause acts as a filter to only include rows that match the specified filter condition—the join condition, in this case. Without the WHERE clause, every row in the first table is paired with every row in the second table, regardless of whether they logically go together.

New Term: Cartesian Product

The results returned by a table relationship without a join condition. The number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table.

To understand this, look at the following SELECT statement and output:

*/
SELECT 
		vend_name
,		prod_name
,		prod_price
,		'LS14B - a cartesian product' qnm
FROM 
		vendors
,		products
ORDER 
BY		vend_name
,		prod_name
;
/*
Analysis

As you can see in the preceding output, the Cartesian product is seldom what you want. The data returned here has matched every product with every vendor, including products with the incorrect vendor (and even vendors with no products at all).

Caution: Don’t Forget the WHERE Clause

Make sure all your joins have WHERE clauses; otherwise, SQL Server returns far more data than you want. Similarly, make sure your WHERE clauses are correct. An incorrect filter condition causes SQL Server to return incorrect data.

Tip: Cross Joins

Sometimes you’ll hear the type of join that returns a Cartesian product referred to as a cross join.

In Lesson 13, “Working with Subqueries,” you saw two ways to obtain a list of customers who ordered products in September of 2016, and both solutions used subqueries (one using IN and one using EXISTS). Here is a third solution, this time using an inner join:

*/
SELECT 
		customers.cust_id
,		customers.cust_name
,		'LS14C - inner join' QNM
FROM 
		customers
,		orders
WHERE	1=1
AND		DateDiff	(	month
					,	order_date
					,	'2016-09-01'
					) = 0 
AND		customers.cust_id = orders.cust_id
;
/*
Inner Joins

The join you have been using so far is called an equijoin—a join based on the testing of equality between two tables. This kind of join is also called an inner join. In fact, you may use a slightly different syntax for these joins, specifying the type of join explicitly. The following SELECT statement returns the exact same data as the preceding example:

*/
SELECT 
		customers.cust_id
,		customers.cust_name
,		'LS14D - inner join #2' as qnm
FROM 
		customers 
			INNER JOIN 
				orders	ON 1=1 -- a no-op ~ allows sliding around/testing of the AND ~ other INNER criteria
				AND		customers.cust_id = orders.cust_id
WHERE	1=1 -- a no-op ~ allows sliding around/testing of the AND ~ other WHERE criteria
AND		DateDiff	(	month
					,	order_date
					,	'2016-09-01'
					) = 0 
-- AND		customers.cust_id = orders.cust_id	-- while coded -- in the case of EXPICIT INNER in from -- it was not required 
--													the FROM .. INNER syntax satisfies the join
;
/*
Analysis

The SELECT in this statement is the same as the preceding SELECT statement, but the FROM clause is different. Here, the relationship between the two tables is part of the FROM clause specified as INNER JOIN. When using this syntax, the join condition is specified using the special ON clause instead of a WHERE clause. The actual condition passed to ON is the same as would be passed to WHERE.

Note: Output Ordering

The WHERE syntax join and the INNER JOIN syntax join return the exact same results. However, you may notice that the two forms of joins return results in different orders. Of course, if you specify an ORDER BY clause then regardless of the syntax used the data will be sorted as specified.

Tip: Which Syntax to Use?

Per the ANSI SQL specification, use of the INNER JOIN syntax is preferable. Although using the WHERE clause to define joins is indeed simpler, using explicit join syntax ensures that you will never forget the join condition, and in some cases it can impact performance, too.

*/
/*
Joining Multiple Tables

SQL imposes no limit to the number of tables that may be joined in a SELECT statement. The basic rules for creating the join remain the same. First, list all the tables and then define the relationship between each. Here is an example:

/*
Analysis

This example displays the items in order number 20005. Order items are stored in the orderitems table. Each product is stored by its product ID, which refers to a product in the products table. The products are linked to the appropriate vendor in the vendors table by the vendor ID, which is stored with each product record. The FROM clause here lists the three tables, and the WHERE clause defines both of those join conditions. An additional WHERE condition is then used to filter just the items for order 20005.

Caution: Performance Considerations

SQL Server processes joins at runtime, relating each table as specified. This process can become very resource intensive, so be careful not to join tables unnecessarily. The more tables you join, the more performance degrades.

This degradation can be dramatically decreased (perhaps even eliminated) by effectively creating indexes for all foreign key columns.

Now would be a good time to revisit the example from Lesson 13. As you will recall, this SELECT statement returns a list of customers who ordered product TNT2:
*/
SELECT 
		cust_id			-- added for clarity/example expansion later
,		cust_name
,		cust_contact
,		'LS14F - "INT2" select example as subquery' qnm
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'
												)
					)
;
/*
As mentioned in Lesson 13, using subqueries might not always be the most efficient way to perform complex SELECT operations. So, as promised, here is the same query using joins:
*/
SELECT 
		customers.cust_id		-- added for clarity/expansion of example
								-- when UNQUALIFIED SQL Server underlines with a
								-- red squiggle -- just like spell check 😘
,		cust_name
,		cust_contact
,		'LS14G - "INT2" example as equi-join' qnm
FROM 
		customers
,		orders
,		orderitems
WHERE	1=1			-- no-op allows AND ~ other WHERE criteria to be slid around/tested
AND		customers.cust_id		= orders.cust_id  
AND		orderitems.order_num	= orders.order_num  
AND		prod_id					= 'TNT2'
;
/*
Analysis

Tip: It Pays to Experiment

As you can see, there is often more than one way to perform any given SQL operation. And there is rarely a definitive right or wrong way. Performance can be affected by the type of operation, the amount of data in the tables, whether indexes and keys are present, and a whole slew of other criteria. Therefore, it is often worth experimenting with different selection mechanisms to find the one that works best for you.

Summary

Joins are one of the most important and powerful features in SQL, and using them effectively requires a basic understanding of relational database design. In this lesson, you learned some of the basics of relational database design as an introduction to learning about joins. You also learned how to create an equijoin (also known as an inner join), which is the most commonly used form of join. In the next lesson, you learn how to create other types of joins.
*/

View schema
Execution time: 0,08 sec, rows selected: 108, rows affected: 55, absolute service time: 0,44 sec