SQL Server set operation
set operation allows us combine or select common data from more 2 tables/ queries. If you want to get data from more than two tables then you will have to repeat set operator between each query.
There are basically 3 types of set operations like
- Union
- Union all (special case A + B)
- Intersection
- Except (Minus)
For demo we will use the below query to create a table and use it throughout out examples
CREATE TABLE Product1 (ProductID INT, ProductName VARCHAR(15))
CREATE TABLE Product2 (ProductID INT, ProductName VARCHAR(15))
INSERT INTO Product1 VALUES (1,'Pen'),(3,'Notebook'),(4,'Pin'),(6,'Punch'),(7,'Glue')
INSERT INTO Product2 VALUES (1,'Pen'),(2,'Stapler'),(4,'Pin'),(8,'Book'),(9,'Glitter')
UNION
union allows us to combine data from two different queries/tables. If the data is common between 1st and 2nd query then it gives distinct data. The output of union is sorted.
A = {1,2,3,6}, B = {2,3,4}
UNION - A U B = {1,2,3,4,6}
Example
SELECT ProductID, ProductName FROM Product1
UNION
SELECT ProductID, ProductName FROM Product1
UNION ALL
union all is slightly different than union where data from 2nd query directly gets appended below the first query. Union is always faster than union operation as the data is simple appended and not distinct or sorting operation is done.
SELECT ProductID, ProductName FROM Product1
UNION ALL
SELECT ProductID, ProductName FROM Product1
INTERSECT
Intersect gives you the common data between two queries.
A = {1,3,5}
B = {1,5,7}
A ∩ B = {1,5}
SELECT ProductID, ProductName FROM Product1
INTERSECT
SELECT ProductID, ProductName FROM Product1
EXCEPT (MINUS)
This operator gives the result from first query which are not present in 2nd query
A = {1,3,5}
B = {1,2,5}
A - B ={3}
SELECT ProductID, ProductName FROM Product1
EXCEPT
SELECT ProductID, ProductName FROM Product1
Considerations of set operations
While applying set operator make sure the data type of the column position between the query must be same. for e.g in above queries ProductID in query 1 and 2 at position 1 are both INT and ProductName is in position 2 with data type as VARCHAR. Since set operation allows columns of same data types as same position, make sure that you places columns in correct position, for instance you should not swap two varchar field else you might get wrong result.
although the query will compile correctly.
Common questions which you can get from set operations are below.
- Difference between union and union all
- Get list of products which were sold in july-2021 but not in june-2021.
- Get list of product which were sold both in june-2021 and july-2021
Comments
Post a Comment