We are on a mission to address the digital
skills gap for 10 Million+ young professionals,
train and empower them to forge a career
path into future tech
Set Operators
Please download pictures in
suitable size here and insert them
by clicking the symbol above.
2 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Overview
• SQL provides set operators to compare rows from two or more tables or to combine the results
obtained from two or more queries to obtain the result.
• These operators are used to join the results of two or more SELECT statements.
• We can use set operators that list the results from the given tables to a single result or in different
rows.
3 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
How to Use Set Operations in SQL?
• SQL supports set operators, which can be performed on the data.
• These operators are used to get the desired results from the table data stored in the table.
• The set operators look like the SQL joins, but there is a big difference.
• SQL joins combine the columns from different tables, whereas SQL operators combine rows from
different queries.
• There are different set operators that you can use to filter out the required data depending on your
needs, and those operators are discussed below with examples.
4 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
How to Use Set Operations in SQL?
• Let's see how you can use the Set Operators:
Example:
SELECT first_select_query
set_operator
SELECT second_select_query
• The above example shows two SELECT queries with the set operator in the middle.
• As mentioned above in the rules, if you select two columns in the first query, you must select two
columns in the second query.
5 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Rules for Set Operators in SQL
1. Number of Columns Must Be the Same
2. Column Data Types Must Be Compatible
3. Column Names Do Not Need to Be the Same
6 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Types of Set Operations
• There are different types of set operators that are mentioned below:
• UNION
• UNION ALL
• MINUS
• INTERSECT
Note :
• while MySQL directly supports UNION and UNION ALL, it does not support MINUS and
INTERSECT directly.
• In MySQL, you can achieve the functionality of the INTERSECT operator using an
INNER JOIN or a EXISTS clause and Minus/Except using LEFT JOIN or NOT EXISTS `
7 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Types of Set Operations
• Let us look into each set operator in more detail with the Employees_US and Employees_UK table as
given below
emp_id emp_name department
1 Alice HR
2 Bob IT
3 Carol Marketing
emp_id emp_name department
2 Bob IT
4 Dave HR
5 Eve Finance
8 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Union
• UNION combines the results of two or more SELECT statements.
• To successfully execute the operation of UNION, the number of columns and the data type must be
the same in both tables.
• After performing the UNION operation, the duplicate rows will be eliminated from the results.
• The syntax of the UNION operator is shown below:
Syntax:
SELECT expression_1, expression_2, ... , expression_n
FROM table_1
UNION
SELECT expression_1, expression_2, ... , expression_n
FROM table_2
9 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Union
• Now, let's take an example to clearly understand how the UNION operator works.
Example:
SELECT emp_id, emp_name, department FROM Employees_US
UNION
SELECT emp_id, emp_name, department FROM Employees_UK;
emp_id emp_name department
1 Alice HR
2 Bob IT
3 Carol Marketing
4 Dave HR
5 Eve Finance
Explanation: The row (2, Bob, IT) appears in both tables but is included only once because UNION
removes duplicates.
10 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Union All
• UNION ALL is also used to combine the results of two or more SELECT statements.
• UNION and UNION ALL are similar in their functioning, but there is a slight difference.
• To successfully execute the operation of Union All, the number of columns and the data type must
be the same in both tables.
• After performing the UNION ALL operation, the duplicate rows will not be eliminated from the
results, and all the data is displayed in the result without removing the duplication.
11 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Union All
Syntax:
SELECT expression_1, expression_2, ... , expression_n
FROM table_1 UNION ALL
SELECT expression_1, expression_2, ... , expression_n
FROM table_2
• Now, let's take an example to clearly understand how the UNION ALL operator works.
SELECT emp_id, emp_name, department FROM Employees_US
UNION ALL
SELECT emp_id, emp_name, department FROM Employees_UK;
12 SQL | © SmartCliff | Internal | Version 1.0
Set Operator
Union All
• The result of the above query is shown below:
emp_id emp_name department
1 Alice HR
2 Bob IT
3 Carol Marketing
2 Bob IT
4 Dave HR
5 Eve Finance
• Explanation: The row (2, Bob, IT) appears twice because UNION ALL does not remove duplicates.
13 SQL | © SmartCliff | Internal | Version 1.0
THANK YOU
