Comparing Table Data Using UNION, EXCEPT, and INTERSECT in SQL Server
The easiest way to compare two tables in SQL Server T-SQL is by utilizing the UNION, EXCEPT, and INTERSECT operators. These operators allow you to identify common rows, remove duplicates, and find differences between tables. UNION combines row sets, EXCEPT removes common rows from the first set, and INTERSECT identifies common rows in both sets. Example queries provided illustrate how to implement these operations effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Practice 10 SQL server 2014 Management studio UNION, EXCEPT and INTERSECT Dr. Haider Haddad
UNION, EXCEPT and INTERSECT SQL Server T-SQL Comparing table data/structure using UNION, EXCEPT and INTERSECT An easiest way to compare two tables is by using UNION, EXCEPT and INTERSECT operators. These operators takes two row sets as input, the output depends on the operator: UNION : produces a new row set by joining both row sets, identifying common rows in input row sets and duplicates are removed, EXCEPT : produces a row set by removing all rows from first row set which are present in second row set, INTERSECT : produces a row set by identifying common rows in both row sets.
1- Union For example, I have below two tables with common data: 1. Removing duplicates from result set (UNION): Common rows can be identified using UNION as below: SELECT ProductID, Name FROM ProductsA UNION SELECT ProductID, Name FROM ProductsB
This statement first removes all rows from result set of ProductsB which are already present in result set of ProductsA, removes duplicates from first result set, and then joins both result sets to produce the final result set: Result Set: ProductID Name 1 Adjustable Race 2 Bearing Ball 3 BB Ball Bearing 23 Bike Stand 64 Cable 77 Bike Wash 87 All-Purpose Bike Stand 92 Chain 316 Blade 316 Blade Runner 324 Chain Stays 712 AWC Logo Cap 843 Cable Lock 877 Bike Wash 952 Chain
2. Removing common rows from result set (EXCEPT): You can remove rows from a result set which are already in another result set by using EXCEPT as: SELECT ProductID, Name FROM ProductsA EXCEPT SELECT ProductID, Name FROM ProductsB This will produce a result set by returning all rows from ProductsA, and then removing all rows which are returned by ProductsB. Result Set: ProductID Name 77 Bike Wash 87 All-Purpose Bike Stand 92 Chain 316 Blade 843 Cable Lock (5 row(s) affected)
3. Identifying common rows (INTERSECT): INTERSECT identifies common rows in the input result sets and returns a result set which is a set of common rows for both result sets: SELECT ProductID, Name FROM ProductsA INTERSECT SELECT ProductID, Name FROM ProductsB Result Set: ProductID Name 1 Adjustable Race 2 Bearing Ball 3 BB Ball Bearing 324 Chain Stays 712 AWC Logo Cap (5 row(s) affected)
Comparing Two Tables: To compare two tables we need a combination of all these operators, Above tables can be compared using below T- SQL: SELECT ProductID, Name, 'Only in ProductsA' FROM ProductsA EXCEPT SELECT ProductID, Name, 'Only in ProductB' FROM ProductsB UNION ALL SELECT ProductID, Name, 'Only in ProductsB' FROM ProductsB EXCEPT SELECT ProductID, Name, 'Only in ProductsA' FROM ProductsA UNION ALL SELECT ProductID, Name, 'In Both Tables' FROM ProductsB INTERSECT SELECT ProductID, Name, 'In Both Tables' FROM ProductsA First two SELECTs identify unique rows in ProductsA, second two SELECTs identify unique rows in ProductsB, and last two statements identify common rows in both, and final result set is produced by UNION ALLs.
Result Set: ProductID Name 87 All-Purpose Bike Stand Only in ProductsA 77 Bike Wash Only in ProductsA 316 Blade Only in ProductsA 843 Cable Lock Only in ProductsA 92 Chain Only in ProductsA 23 Bike Stand Only in ProductsB 64 Cable Only in ProductsB 316 Blade Runner Only in ProductsB 877 Bike Wash Only in ProductsB 952 Chain Lock Only in ProductsB 1 Adjustable Race In Both Tables 2 Bearing Ball In Both Tables 3 BB Ball Bearing In Both Tables 324 Chain Stays In Both Tables 712 AWC Logo Cap In Both Tables (15 row(s) affected)
Join with 3 tables Orders Customers Sales Rep.
Solve the queries below 1-List orders over $25,000, including the name of the salesperson who took the order and the name of the customer who placed it. 2- which person take product bulldozer 3- Get the company name of jen jones
List orders over $25,000, including the name of the salesperson who took the order and the name of the customer who placed it. SELECT OrderNbr, Amt, Company, Name FROM Orders, Customers,SalesReps WHERE Cust = CustNbr AND CustRep = RepNbr AND Amt >=25000 SELECT OrderNbr, Amt,Company,Name FROM salesReps iNNER jOIN customers ON SalesReps.RepNbr = customers.CustRep INNER JOIN Orders ON Customers.CustNbr = Orders.Cust WHERE Amt >= 25000;
