Set Operators in SQL

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 similar to 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.

There are some rules that you have to follow while applying set operators in SQL. These are mentioned below:

• The number of columns in the SELECT statement on which you want to apply the SQL set operators must be the same.
• The order of columns must be in the same order.
• The selected columns must have the same data type.
• If you want to order/sort the results, the ORDER BY clause must go at the end of the last query. You can't add ORDER BY inside each SELECT query before the set operators.

Let's see how you can use the Set Operators:

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. The data types also need to be compatible, i.e., if you select two character types in the first query, you must also do the same in the second query.

Types of Set Operations

There are different types of set operators that are mentioned below:

• UNION
• UNION ALL
• MINUS
• INTERSECT

Let us look into each set operator in more detail with examples. This article uses the following two tables to execute the queries. All the operations are performed on the comp1_employees and comp2_employees table that is given below.

comp1_employees:

comp2_employees:

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:

SELECT expression_1, expression_2, ... , expression_n
FROM table_1
UNION
SELECT expression_1, expression_2, ... , expression_n
FROM table_2

Now, let's take an example to clearly understand how the UNION operator works.

SELECT * FROM comp1_employees 
UNION  
SELECT * FROM comp2_employees;

The result of the above query is shown below:

As shown in the above code snippet, there are two SELECT queries, and a UNION operator is used. The first SELECT query will fetch the records from comp1_employees, and the second SELECT query will fetch the records from comp2_employees, and the UNION operation is performed with the results of both the query.

After performing the UNION operation with both the tables, all the records from the comp1_employees table and comp2_employees table are displayed except for the duplicate data, i.e., employee_id -- 2 and $7$ are duplicates. Hence, they are displayed only one time.

Union All

UNION and UNION ALL are similar in their functioning, but there is a slight difference. UNION ALL is also used to combine the results of two or more SELECT statements. 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.

The syntax of the UNION ALL operator is shown below:

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 * FROM comp1_employees 
UNION ALL
SELECT * FROM comp2_employees;

The result of the above query is shown below:

As shown in the above code snippet, there are two SELECT queries, and a UNION All operator is used. The first SELECT query will fetch the records from comp1_employees, and the second SELECT query will fetch the records from comp2_employees, and the UNION ALL operation is performed with the results of both the query.

After performing the UNION ALL operation with both tables, all the records from the comp1_employees table and comp2_employees table are displayed. Since it's a UNION ALL operation, all the records are displayed, including the duplicate words, which is not the case in the UNION operation.

Intersect

The INTERSECT operator allows you to find the results that exist in both queries. To successfully execute the operation of INTERSECT, the number of columns and the data type must be the same in both tables. After performing the INTERSECT operation, the data/records which are common in both the SELECT statements are returned.

The syntax of the INTERSECT operator is shown below:

SELECT expression_1, expression_2, ... , expression_n
FROM table_1
INTERSECT
SELECT expression_1, expression_2, ... , expression_n
FROM table_2

Now, let's take an example to clearly understand how the INTERSECT operator works.

SELECT * FROM comp1_employees 
INTERSECT
SELECT * FROM comp2_employees;

The result of the above query is shown below:

As shown in the above code snippet, there are two SELECT queries, and an INTERSECT operator is used. The first SELECT query will fetch the records from comp1_employees, and the second SELECT query will fetch the records from comp2_employees, and the INTERSECT operation is performed with the results of both the query.

After performing the INTERSECT operation with both tables, all the data/records that are common from the comp1_employees table and comp2_employees table are displayed. As you can see in the result above, the INTERSECT gets the results that exist in both queries. And the employee_id -- 2 and $7$ exist in both tables; hence they are displayed in the final result.

Minus/Except

The MINUS operator allows you to filter out the results which are present in the first query but absent in the second query. To successfully execute the MINUS operation, the number of columns and the data type must be the same in both tables. After performing the MINUS operation, the data/records which are not present in the second SELECT statement or query are displayed.

The syntax of the EXCEPT operator is shown below:

SELECT expression_1, expression_2, ... , expression_n
FROM table_1
EXCEPT
SELECT expression_1, expression_2, ... , expression_n
FROM table_2

Let's take an example to clearly understand how the EXCEPT operator works in MySQL, which is similar to the MINUS operator.

SELECT * FROM comp1_employees 
EXCEPT
SELECT * FROM comp2_employees;

The result of the above query is shown below:

As shown in the above code snippet, there are two SELECT queries, and an EXCEPT operator is used. The first SELECT query will fetch the records from comp1_employees, and the second SELECT query will fetch the records from comp2_employees, and the EXCEPT operation is performed with the results of both the query.

After performing the EXCEPT operation with both tables, all the data/records that are present in the comp1_employees table but not in the comp2_employees table are displayed. As you can see in the result above, the EXCEPT gets the results that are present only in the comp1_employees without any duplicates.

Cartesian Product Using Join

The Cartesian join, also called Cross join, is used to generate a paired combination of each row of the first table with each row of the second. That means it results in the Cartesian product of two or more tables.

If you don't specify a condition when joining two tables, the database system combines each row from the first table with each from the second. This type of join is called Cartesian join or Cross join.

Below are the two tables of employees and departments on which Cartesian join is applied in the example below.

Let's take an example to clearly understand how the Cartesian join works.

SELECT * FROM employees
CROSS JOIN dept

The result of the above query is shown below:

The above query will join the employees and dept table with the CROSS JOIN keyword, and the result obtained is the paired combination of the employees and dept tables, i.e., after applying cross join or cartesian join, each row from the employees table is combined with each row from the dept table.