Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Microsoft SQL Server T-SQL in 10mn ~ Lesson 15. Creating Advanced Joins samples...
/* */ -- 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. */
run
|
edit
|
history
|
help
0
Using Variables
abhishek
Test row_number() ordering when CTE referenced multiple times
table emp
string splitter
Lab assessment 2
VANYA
Test1
Stored Procedure
Sample