The Different Types of Set Operators SQL With Practical Examples
Summary: In this tutorial, you will learn the different types of Set Operators SQL Server With Practical Examples. Have you seen keywords such as UNION, INTERSECT, or MINUS and want to know what they do and why they’re used? Letus see Set Operators SQL.
What is SQL Set Operators?
Set Operators SQL Server is basically used to combine the identical type of data from two or more tables. Furthermore, although more than one select statement will then be present, it will be returned only one result set. SQL Database supports three set operators, such as UNION, EXCEPT, and INTERSECT.
A set operator is a keyword in a SQL Server, that lets you combine the results set of two queries into a single query.
Sometimes once operating with SQL, you’ll have a requirement to query data from two more tables. Furthermore, but instead of joining these two tables, then you will need to list the results from both tables in a single result. That’s what set operators do.
Set Operators SQL: Four Set Operators:
There are four-set operators in SQL Server, such as union, union all, intersect, and except which allow us to combine two or more select statements.
| SQL Set Operator | Returns |
|---|---|
| Union | It allows us to combine two or more result sets into a single set, without duplicates. |
| Union All | It also allows us to combine two or more result sets into a single set, but including all duplicates. |
| Intersect | This will take the data from both result sets which are in common. |
| Minus | Takes the data from the 1st result set, but not the 2nd means that there is no matching to each other. |
There are the Rules on Set Operations:
- The result sets of all queries should have an identical number of columns.
- In every result set, the data type of every column should match the data type of its respective column within the 1st result set.
- Therefore, in order to sort the result, an ORDER BY keyword should be part of the last statement.
- Furthermore, the top query from records must match with the positional ordering of the bottom query from the records
- The column names or aliases should be found out by the 1st select statement.
Syntax For Set Operators SQL
|
1 2 3 4 5 |
SELECT [ColumnName, . . . ] FROM [TableName1] [set operator] SELECT [ColumnName, . . .] FROM [TableName2] [set operator] ... ... SELECT [ColumnName, . . . ] FROM [TableNameN] |
Example Set Operators SQL
Let’s create two tables with the same or identical column name and data type.
|
1 2 3 4 5 6 7 |
CREATE TABLE New_Employees( Name VARCHAR(50), TotalSalary INT) CREATE TABLE Old_Employees( Name VARCHAR(50), TotalSalary INT) |
Let us insert records a few values into the tables.
|
1 2 3 4 5 6 7 8 |
INSERT INTO New_Employees VALUES('Elizabeth Brown',28600); INSERT INTO New_Employees VALUES('Victoria Ashworth',18500); INSERT INTO New_Employees VALUES('Roland Mendel',30200); INSERT INTO New_Employees VALUES('Lino Rodriguez',40500); INSERT INTO Old_Employees VALUES('Elizabeth Brown',28600); INSERT INTO Old_Employees VALUES('Roland Mendel',30200); INSERT INTO Old_Employees VALUES('Alfreds Futterkiste',25900); INSERT INTO Old_Employees VALUES('Sven Ottlieb',26400); |
Result
ResultSet for New_Employees table
ResultSet for Old_Employees table
A) UNION Operation
The SQL UNION Operator is used to combine two tables using select statements when both tables have the same number of columns.
Union works like Distinct. Union all DOES NOT do distinct.
|
1 2 |
SELECT Name,TotalSalary FROM New_Employees UNION SELECT Name,TotalSalary FROM Old_Employees |
The Elizabeth Brown and Roland Mendel records are duplicate records. Thus, these are returned only once.
B) UNION ALL Operation
The UNION ALL operator in SQL Server is used to list all records from two or more select statements. Thus, therefore, all the records from both tables must be in the same order.
|
1 2 3 |
SELECT Name,TotalSalary FROM New_Employees UNION ALL SELECT Name,TotalSalary FROM Old_Employees |
Here, Elizabeth Brown and Roland Mendel are stored in both tables. UNION ALL returns all records and including duplicate records.
C) INTERSECT Operation
INTERSECT operator returns any distinct values means they’re returned by both the query on the left and right sides of the INTERSECT operand.
|
1 2 3 |
SELECT Name,TotalSalary FROM New_Employees INTERSECT SELECT Name,TotalSalary FROM Old_Employees |
Only the Elizabeth Brown and Roland Mendel records are returned because they are found in both tables.
D) EXCEPT
EXCEPT clause in SQL Server is functioning as like MINUS operation in Oracle. Therefore, the EXCEPT query in SQL Server returns all rows which are in the 1st query but those are not returned in the 2nd query.
Example-I
|
1 2 3 |
SELECT Name,TotalSalary FROM New_Employees EXCEPT SELECT Name,TotalSalary FROM Old_Employees |
Example-II
Here, during this case, EXCEPT in SQL they will return any distinct values from the left select query that aren’t also found on the right select query.
|
1 2 3 |
SELECT Name,TotalSalary FROM Old_Employees EXCEPT SELECT Name,TotalSalary FROM New_Employees |
Therefore, as per the above described EXCEPT example From the two results, we understand that if any records are found in both tables, they’re removed from the first table’s record set.
Thus, we can say that the four-set operators (union, union all, intersect and except) in SQL all have similar precedence.
Related Question and Answer
I) What’s the Difference between UNION and UNION ALL?
Let us see, the major distinction between UNION ALL and UNION in SQL Server
- The UNION Operator removes any duplicate results from the ultimate result set, but UNION ALL doesn’t. Furthermore, The UNION operation performs a DISTINCT on the result set to remove duplicates.
- Therefore, UNION ALL always shows more results, as it doesn’t remove duplicate records.
- As a result of this, UNION is commonly slower than UNION ALL, Thus, as a result of there’s a function operation to remove duplicate values which is often an expensive step in a query.
- UNION ALL doesn’t perform a definite, thus is sometimes quicker.
- Suppose that you don’t need to have distinctive rows in your result set, or if you’re sure that rows in your database or query are unique already, then use UNION ALL.
II) What’s the Difference between UNION and INTERSECT?
The difference between UNION and INTERSECT is that UNION gets results from both queries and combines them, But in the case of INTERSECT, its gets results that only exist in both queries.
So, if Query 1 returns records A & B, and Query 2 returns records B & C, UNION would return A, B, & C. INTERSECT would only return B.
III) What’s the Difference Between a UNION and a JOIN?
The UNION and JOIN keywords both working function are same, both combine the results from two different tables or queries. The difference is how they are combined.
The UNION operation combines the data into separate rows, and while the JOIN combines data into separate columns. When performing a JOIN, therefore, there is a column that matches between the two tables, & additional data may be displayed.
About Author
