SQL Set Operators | Complete Guide to UNION, INTERSECT & MINUS
Summary: In this tutorial, you will learn about SQL Set Operators – The Complete Guide to UNION, INTERSECT and MINUS operators with practical exmple and Why they are used.
Why SQL Set Operators Are Used?
A SQL Set Operators is a keyword that allows you to combine the results of two queries into a single query.
Sometimes when working with SQL, you’ll have a requirement to query data from two more tables. however, instead of joining these two tables, furthermore, you’ll need to list the results from both tables in a single result, or in different rows. That’s what SQL set operators do.
What are the Different Types of SQL Set Operators
There are some different SQL set operators which can be used, according to your needs.
The different SQL set operators are:
- UNION
- UNION ALL
- MINUS
- INTERSECT
- EXCEPT
Let’s see, take a look at each of these, using some sample data with example
How To Use Set Operators in SQL
The Set operators in SQL are used like as given below:
set_operator
SELECT another_select_query;
Therefore, as per the given above syntax, it uses two or more SELECT queries, with a set operator in the middle.
There are a few things you can notice and keep in mind though.
- When selecting your columns, then the number of columns needs to match between queries, and also, the data type of each column needs to be compatible.
- Furthermore, if you select 3 columns in the 1st query, you need to select three columns in the 2nd query.
- The data types also need to be compatible, so if you select a number & two character types in the 1st query, then you need to do the same in the 2nd query.
- Also, if you wish to order your results, then the ORDER BY must go at the end of the last query.
- Therefore, you can’t add ORDER BY inside every SELECT query before the SQL set operator.
Sample Demo Data
Customer
Employee
Demonstration Of Example
A) UNION: Combined Results Without Any Duplicate
The UNION keyword in SQL is used to combine the results of two queries. Furthermore, this keyword removes any duplicate results and shows you the combination of both.
Expressed as a Venn diagram, where every rectangle represents a query result, furthermore, it looks like this:
Let’s see an example, which will be based on the sample data above. suppose that if we wanted to select from both the employee and customer tables, using UNION, then our query would look like this as given below:
|
1 2 3 4 5 |
SELECT CustomerName, City FROM Customer UNION SELECT EmployeeName, City FROM Employee; |
The above result screenshot shows us all customer and employee records.
Here, you will notice that there are all records in this table that appear one time, when some records are available twice such as (Abhishek Thakur, Shivaditya Shukla, and Pushpendra Verma). Why is that? because of UNION, remove all duplicates.
To order the results, Then you need to use the ORDER BY at the End
|
1 2 3 4 5 6 |
SELECT CustomerName, City FROM Customer UNION SELECT EmployeeName, City FROM Employee ORDER BY City; |
B) UNION ALL: Combined Results With Duplicate
The SQL UNION ALL set operator also combines the results from two queries. Therefore, it’s very similar to the SQL UNION operator, but it doesn’t remove duplicates.
Expressed as a Venn diagram, it looks like as given below:
Let’s see UNION ALL example. If we wanted to select from both the customer and employee tables, using SQL UNION ALL Operator, our query would look like as given below:
|
1 2 3 4 5 |
SELECT CustomerName, City FROM Customer UNION ALL SELECT EmployeeName, City FROM Employee; |
The above result screenshot shows us all customer and employee records.
Here, you will notice that there are some records in this table that appear twice (Abhishek Thakur, Shivaditya Shukla, and Pushpendra Verma). Why is that?
because of UNION, ALL doesn’t remove duplicates. So the same CustomerName and City values exist in both the customer and employee tables, and this query shows each of them.
C) INTERSECT: To Find Results That Exist In Both Queries
The SQL INTERSECT keyword permits you to find results that exist in both queries. Therefore, two SELECT statements are required, and any results that are found in both of them are returned if INTERSECT is used.
Thus, INTERSECT Venn diagram looks like this:
Therefore, To use our example data, we could use the SQL INTERSECT set operator to find all names in the customer table that don’t exist in the employee table.
Thus, our query would look like as given below:
|
1 2 3 4 5 |
SELECT CustomerName, City FROM Customer INTERSECT SELECT EmployeeName, City FROM Employee; |
The above result screenshot shows us all customer and employee records.
Here, you will notice SQL INTERSET Operator shows the duplicate records which are available in both tables. Why is that? because INTERSECT finds results that exist in both queries.
C) MINUS: Finding Results That Are Missing
The MINUS Keyword in SQL will return results that are found in the 1st query specified that don’t exist in the 2nd query.
The Venn diagram represents, as given below:
Let’s use our demo data, Here, we will use the SQL MINUS operator to find all names in the customer table that doesn’t exist in the employee table.
The given below query would look like this:
|
1 2 3 4 5 |
SELECT CustomerName, City FROM Customer MINUS SELECT EmployeeName, City FROM Employee; |
Related Question and Answer
I) What’s the Difference Between a UNION and a JOIN?
- The SQL UNION and SQL JOIN keywords both combine results from two different tables or queries.
- SQL UNION keyword combines data into separate rows when SQL JOIN keyword combines data into separate columns.
- When performing a JOIN in SQL, there’s a column that matches between the two tables, and additional data may be displayed.
If we wanted to JOIN our employee and customer tables, then our query might look like as given below:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT e.EmployeeID, e.EmployeeName, e.City, c.CustomerID, c.CustomerName, c.City FROM Employee e INNER JOIN Customer c ON e.EmployeeName = c.CustomerName AND e.City = c.City; |
II) What’s the Difference between UNION and UNION ALL?
- The difference between SQL UNION ALL and UNION is that UNION can remove any duplicate results from the final result set, and SQL UNION ALL does not.
- SQL UNION can perform a DISTINCT on the result set to remove duplicates.
- Always keep in mind this, consider that “ALL” in UNION ALL means “it shows all records”.
- Furthermore, UNION ALL will always show more results, because it doesn’t remove duplicate records.
- Thus, The UNION operator is slower than UNION ALL operator, because it removes duplicate values (a.k.a DISTINCT), which is commonly an expensive step in a query.
- UNION ALL doesn’t perform a distinct, thus is sometimes quicker.
- So finally, we can say that if you don’t need to have unique rows in your result set, or if you’re sure that the rows in the database or query are unique already, then use UNION ALL.
III) What’s the Difference between UNION and INTERSECT?
The difference between SQL UNION operator and SQL INTERSECT operator is that UNION gets results from both queries and combines them, while SQL INTERSECT operator gets results that only exist in both queries.
So, if Query 1 returns record A and B, and Query 2 returns records B and C, UNION would return A, B, and C. INTERSECT would only return B.
IV) What is the EXCEPT Set Operator?
- The EXCEPT keyword is used in the place of MINUS, when in SQL Server Management Studio MINUS keyword not working. it is another set operator.
- EXCEPT working operation is the same as MINUS – they both show results from one query that don’t exist in another query.
- However, SQL MINUS is an Oracle-specific keyword, and EXCEPT is in other databases like SQL Server.
- So, if you see EXCEPT anyplace, simply recognize it’s constant as MINUS except for a different database.
Conclusion:
In this article, you have learned that SQL Set Operators can be useful when you want to combine the results of different queries into different rows. In SQL, the different set operators are UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT, depending on your database). and also learned how to use Set operators in SQL with a practical example. I hope you will enjoy it!
About Author
