SET Operators in SQL

What is a SET Operator in SQL?

A SET operator in SQL combines the results of two or more SELECT statements or compares rows from two or more tables. It returns a single result set by eliminating duplicate rows.

Syntax

The syntax of SET operators in SQL is as follows:

Parameters

The parameters of SET operators in SQL include:

• UNION returns all distinct rows selected by either query, eliminating duplicates.
• UNION ALL returns all rows selected by either query, including duplicates.
• INTERSECT returns only the rows common to both queries.
• EXCEPT returns only the rows unique to the first query.

How to Use SET Operators in SQL?

SQL supports set operators, which 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. Unlike SQL joins that combine columns from different tables, set operators combine rows.

There are some rules to follow while applying set operators, as discussed 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.

Types of SQL SET Operators

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:

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

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:

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

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:

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

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:

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

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.

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.