In this tutorial, we’ll look at the set operators used in TSQL. Set operators and Joins work very similar but the biggest difference is that, in set operators, the result set is a superimposed table made out of the two tables used in the select statement. That is, the columns are not repeated, the values from the two tables will fall under the same column name similar to the two tables. There are 4 set operators, there are Union, Union All, Except, Intersect.
The general syntax for a set operator looks like this
SELECT col1,col2,col3
FROM T1
<set operator>
SELECT col1,col2, col3
FROM T2
There are some general rules about set operators and it’s worth remembering those rules when using these operators.
Rule 1 : The number of columns in first select statement must be same as the number of columns in the second select statement
Rule 2: The metadata of all the columns in first select statement must be exactly same as the metadata of all the columns in second select statement accordingly.
Rule 3: order by clause do not work with first select statement
Lets create two tables and see how to apply set operators to retrieve information.
CREATE TABLE t1(id int, name varchar(50))
CREATE TABLE t2(id int, name varchar(50))
INSERT INTO t1
VALUES(1,’a’),(2,’b’),(2,’b’),(3,’c’),(4,’d’)
INSERT INTO t2
VALUES(3,’e’),(4,’d’),(5,’z’),(6,’x’)
Now lets see how the set operators work.
UNION ALL — gives the union of the two select statements. it will give duplicates if it exist.
SELECT *
FROM t1
UNION ALL
SELECT *
FROM t2
UNION — will give you the union of the two select statements but union will not give duplicates if it exists.
SELECT *
FROM t1
UNION
SELECT *
from t2
INTERSECT– gives only overlapping results from the two select statements
SELECT *
FROM t1
INTERSECT
SELECT *
FROM t2
EXCEPT– gives everything from first set except those that are in the intersect of first set and the second set
SELECT * from t1
EXCEPT
SELECT * from t2
Let’s see some application of some of this set operator to real problems like we did for joins in the last lesson. You can check the tutorial 12 to look at the table l’m going to use to continue the application
Let us find the list of employeeids of employees who are managers
SELECT employeeid
FROM employee
INTERSECT
SELECT employeeid
FROM Employee
WHERE employeeID in(SELECT DISTINCT managerid
FROM employee
WHERE managerid IS NOT NULL)
The above uses a sub query. we’ll be discussing sub queries next so if you have some problem understanding the last part, hold on it and you can clear that up during the sub query tutorial. The last where statement WHERE managerid IS NOT NUL is for restriction of NULL values for managerid columns that have NULL because the current employee doesn’t have a manager. For example, the CEO of the company is an employee too and his manager id column will definitely be NULL because he doesn’t have any manager. This is just based on the hypothetical business rule l’m using for this tutorial.
Another application, lets find list of employeeids of employees who are not managers
SELECT employeeid
FROM employee
EXCEPT
SELECT employeeid
FROM employee
WHERE employeeid IN(SELECT DISTINCT ManagerID
FROM Employee
WHERE ManagerID IS NOT NULL)
Hope this is useful for somebody, l’ll be discussing sub queries in the next tutorial. Keep in touch, Peace.
