SET Operators in SQL

SET operators are special type of operators which are used to combine the result of two queries.

Operators covered under SET operators are:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

There are certain rules which must be followed to perform operations using SET operators in SQL. Rules are as follows:

1. The number and order of columns must be the same.
2. Data types must be compatible.

Let us see each of the SET operators in more detail with the help of examples.

All the examples will be written using the MySQL database.

Consider we have the following tables with the given data.

Table 1: t_employees

Table 2: t2_employees

Table 3: t_students

Table 4: t2_students

1. UNION:

• UNION will be used to combine the result of two select statements.
• Duplicate rows will be eliminated from the results obtained after performing the UNION operation.

Example 1:

Write a query to perform union between the table t_employees and the table t2_employees.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_employees table and perform a UNION operation with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

Since we have performed union operation between both the tables, so only the records from the first and second table are displayed except for the duplicate records.

Example 2:

Write a query to perform union between the table t_students and the table t2_students.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_students table and perform a UNION operation with the records fetched by the second SELECT query from the t2_students table.

You will get the following output:

Since we have performed union operation between both the tables, so only the records from the first and second table are displayed except for the duplicate records.

2. UNION ALL

• This operator combines all the records from both the queries.
• Duplicate rows will be not be eliminated from the results obtained after performing the UNION ALL operation.

Example 1:

Write a query to perform union all operation between the table t_employees and the table t2_employees.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_employees table and perform UNION ALL operation with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

Since we have performed union all operation between both the tables, so all the records from the first and second table are displayed, including the duplicate records.

Example 2:

Write a query to perform union all operation between the table t_students and the table t2_students.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_students table and perform UNION ALL operation with the records fetched by the second SELECT query from the t2_students table.

You will get the following output:

Since we have performed union all operation between both the tables, so all the records from the first and second table are displayed, including the duplicate records.

3. INTERSECT:

• It is used to combine two SELECT statements, but it only returns the records which are common from both SELECT statements.

Example 1:

Write a query to perform intersect operation between the table t_employees and the table t2_employees.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_employees table and perform INTERSECT operation with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

Since we have performed intersect operation between both the tables, so only the common records from both the tables are displayed.

Example 2:

Write a query to perform intersect operation between the table t_students and the table t2_students.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_students table and perform a UNION operation with the records fetched by the second SELECT query from the t2_students table.

You will get the following output:

Since we have performed intersect operation between both the tables, so only the common records from both the tables are displayed.

4. MINUS

• It displays the rows which are present in the first query but absent in the second query with no duplicates.

Example 1:

Write a query to perform a minus operation between the table t_employees and the table t2_employees.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_employees table and perform MINUS operation with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

Since we have performed Minus operation between both the tables, so only the unmatched records from both the tables are displayed.

Example 2:

Write a query to perform a minus operation between the table t_students and the table t2_students.

Query:

Here, in a single query, we have written two SELECT queries. The first SELECT query will fetch the records from the t_employees table and perform a UNION operation with the records fetched by the second SELECT query from the t2_employees table.

You will get the following output:

Since we have performed a minus operation between both the tables, so only the Unmatched records from both the tables are displayed.