If you have been writing queries in SQL, there must be various situations where you wanted to join the results obtained from two or more queries into a single result set, and you have wondered, “Is it possible to do that in SQL?”, Yes, it’s possible! In this tutorial, we will learn about different SET operators through examples. Venn diagrams will show how each operator works and differs from other SET operators for better visualisation.
Introduction to SQL SET Operators
The SET operators in SQL combine and compare the results of two or more SELECT statement queries into a single result. Instead of combining columns as in SQL joins, they function by merging rows from two or more tables. Mathematical operations and set operators are similar. It’s important to comprehend how joins and SQL set operators operate differently. UNIONs efficiently combine/exclude rows from tables vertically (top to bottom), whereas SQL JOINs use columns to join tables horizontally (left to right).
Syntax:
SELECT column01, column02
FROM table01
UNION/UNION ALL/INTERSECT/EXCEPT
SELECT column01, column02
FROM table02;Code language: SQL (Structured Query Language) (sql)Also Read: MySQL ANY and ALL Operators
Types of SQL SET Operators
There are four main types of SQL SET Operators:
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
In this section, we will use these two tables, i.e., Cricketers and Actors for demonstrating the use of each type.
1. UNION
The result of two or more SELECT queries is combined using UNION. Both tables must have the same number of columns and data types in order for UNION to function properly. Duplicate rows will be removed from the results after executing the UNION operation.
Syntax:
SELECT column01, column02
FROM table01
UNION
SELECT column01, column02
FROM table02;Code language: SQL (Structured Query Language) (sql)Example:
We would use the following statement to fetch the data of all the Cricketers and Actors, excluding the duplicate value.
SELECT name
FROM Cricketers
UNION
SELECT name
FROM Actors;Code language: SQL (Structured Query Language) (sql)Output:
The above table shows that the first SELECT statement will fetch the records from Cricketers, and the second SELECT statement will fetch the records from Actors. The UNION operation is performed with the results of both the query. All the records from the Cricketers table and ACTORS table are fetched when the UNION operation is completed with both tables, excluding the duplicate data (i.e., id 3 from table 1 and 2 from table 2 are duplicates). As a result, they are only shown once.
2. UNION ALL
The result of two or more SELECT queries is combined using UNION ALL. Both tables must have the same number of columns and data types in order for UNION ALL to function properly. The only difference is that UNION ALL keeps duplicate rows in all the tables.
Syntax:
SELECT column01, column02
FROM table01
UNION ALL
SELECT column01, column02
FROM table02;Code language: SQL (Structured Query Language) (sql)Example:
We would use the following statement to fetch the data of all the Cricketers and Actors, including the duplicate value.
SELECT name
FROM Cricketers
UNION ALL
SELECT name
FROM Actors;Code language: SQL (Structured Query Language) (sql)Output:
The above table shows that the first SELECT statement will fetch the records from Cricketers, and the second SELECT statement will fetch the records from Actors. The UNION ALL operation is performed with the results of both the query. All the records from the Cricketers and Actors tables are fetched when the UNION ALL operation is completed with both tables. Since it is UNION ALL, all the records will include duplicate data (i.e., id 3 from Table 1 and 2 from Table 2 are duplicates).
3. INTERSECT
The INTERSECT operator in SQL is used to combine the result sets of two SELECT statements and returns only the rows that appear in both result sets. Unlike the UNION and UNION ALL operators, which combine and return all rows from both result sets, INTERSECT only returns the common rows to both result sets.
Syntax:
SELECT column01, column02
FROM table01
INTERSECT
SELECT column01, column02
FROM table02;Code language: SQL (Structured Query Language) (sql)Example:
If we want to fetch the common data of the Cricketers and Actors table, we would use the following statement.
SELECT name
FROM Cricketers
INTERSECT
SELECT name
FROM Actors;Code language: SQL (Structured Query Language) (sql)Output:
The above table shows that the first SELECT statement will fetch the records from Cricketers, and the second SELECT statement will fetch the records from Actors. The INTERSECT operation is performed with the results of both the query. All the common records from the Cricketers and Actors tables are fetched when the INTERSECT operation is completed with both tables. Since S. Sreesanth is common in both the table, it is displayed.
4. EXCEPT
The EXCEPT operator in SQL combines the result sets of two SELECT statements and returns only the rows appearing in the first result set but not in the second. EXCEPT filters out the rows present in the second result set from the first result set.
Syntax:
SELECT column01, column02
FROM table01
EXCEPT
SELECT column01, column02
FROM table02;Code language: SQL (Structured Query Language) (sql)Example:
We would use the following statement to fetch the data in Cricketers but not in the Actors table.
SELECT name
FROM Cricketers
EXCEPT
SELECT name
FROM Actors;Code language: SQL (Structured Query Language) (sql)Output:
The above table shows that the first SELECT statement will fetch the records from Cricketers, and the second SELECT statement will fetch the records from Actors. The EXCEPT operation is performed with the results of both the query. The records exclusive to the Cricketers table compared to the Actors table are fetched when the EXCEPT operation is applied between both tables.
Conclusion
In this tutorial, we have learned about SQL set operators and how they can be used in different scenarios. Also, we have learned about the main types of SQL set operators and how and where to use them. SQL set operators are one of the most asked topics during interviews. To learn more about the types of questions that can be asked during SQL interviews, click here!