Set Operators in SQL

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

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 final result. These operators are used to join the results of two (or more) SELECT statements. While working with SQL, you'll need to query the data from two or more tables, and instead of joining these two tables, you can use set operators that list the results from the given tables to a single result or in different rows. That means for some bunch of the problems, set operators are easy to use than joining data.

There are different Set Operators that you can use in SQL according to your needs, and those are explained in this article.

Scope

  • In this article, you'll study what SQL set operators are and how many set operators are available to use in SQL.
  • You'll learn how to use set operators to achieve the desired result while working with SQL.
  • This article covers detailed information about the types of set operators, i.e., UNION, UNION ALL, MINUS, and INTERSECT.
  • You'll also study the Cartesian product using join.

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:

employee_idemployee_nameemployee_city
1Bhim ShekhSurat
2Mehul MohanGoa
3Palash YadavAhmedabad
4Ela ShikhaDelhi
5Mrinal ThakurBangalore
6Sitara VaniBangalore
7Mohak JainGurugram
8Adesh PatelJaipur
9Kunal TandonDelhi
10Romit SoniMumbai

comp2_employees:

employee_idemployee_nameemployee_city
1Sahdev RamiahRaipur
2Mehul MohanGoa
3Mohak JainGurugram
4Pragun SarikaChennai
5Pooja SrivastavaBangalore
6Vani ShekhawatDelhi
7Mohak JainAhmedabad
8Neera ShahAhmedabad
9Poonam OberoiDelhi
10Abhishek SainiRaipur

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:

employee_idemployee_nameemployee_city
1Bhim ShekhSurat
1Sahdev RamiahRaipur
2Mehul MohanGoa
3Mohak JainGurugram
3Palash YadavAhmedabad
4Ela ShikhaDelhi
4Pragun SarikaChennai
5Mrinal ThakurBangalore
5Pooja SrivastavaBangalore
6Sitara VaniBangalore
6Vani ShekhawatDelhi
7Mohak JainGurugram
8Adesh PatelJaipur
8Neera ShahAhmedabad
9Kunal TandonDelhi
9Poonam OberoiDelhi
10Abhishek SainiRaipur
10Romit SoniMumbai

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 77 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:

employee_idemployee_nameemployee_city
1Bhim ShekhSurat
2Mehul MohanGoa
3Palash YadavAhmedabad
4Ela ShikhaDelhi
5Mrinal ThakurBangalore
6Sitara VaniBangalore
7Mohak JainGurugram
8Adesh PatelJaipur
9Kunal TandonDelhi
10Romit SoniMumbai
1Sahdev RamiahRaipur
2Mehul MohanGoa
3Mohak JainGurugram
4Pragun SarikaChennai
5Pooja SrivastavaBangalore
6Vani ShekhawatDelhi
7Mohak JainGurugram
8Neera ShahAhmedabad
9Poonam OberoiDelhi
10Abhishek SainiRaipur

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.

UNION operation removes duplicates from the final result, whereas UNION ALL operation does not remove duplicates and displays all the data.

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:

employee_idemployee_nameemployee_city
2Mehul MohanGoa
7Mohak JainGurugram

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 77 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.

Note: The MINUS operator is supported only in Oracle databases. For other databases like SQLite, PostgreSQL, SQL server, you can use EXCEPT operator to perform similar operations.

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:

employee_idemployee_nameemployee_city
1Bhim ShekhSurat
3Palash YadavAhmedabad
4Ela ShikhaDelhi
5Mrinal ThakurBangalore
6Sitara VaniBangalore
8Adesh PatelJaipur
9Kunal TandonDelhi
10Romit SoniMumbai

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.

employee_idemployee_name
1Bhim Shekh
2Palash Yadav
3Ela Shikha
4Mrinal Thakur



city_iddept_iddept_name
0111Finance
0222Admin
0333Intelligence
0444Serices

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:

employee_idemployee_namecity_iddept_iddept_name
1Bhim Shekh111Finance
1Bhim Shekh222Admin
1Bhim Shekh333Intelligence
1Bhim Shekh444Services
2Palash Yadav111Finance
2Palash Yadav222Admin
2Palash Yadav333Intelligence
2Palash Yadav444Services
3Ela Shikha111Finance
3Ela Shikha222Admin
3Ela Shikha333Intelligence
3Ela Shikha444Services
4Mrinal Thakur111Finance
4Mrinal Thakur222Admin
4Mrinal Thakur333Intelligence
4Mrinal Thakur444Services

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.

Conclusion

  • SQL supports various set operators, which you can use to filter out the desired data from the SELECT queries/tables.
  • The number of columns in the SELECT statement on which you want to apply the SQL set operators and the data type must be the same.
  • The UNION operator combines the result of two or more SELECT statements and eliminates the duplicate rows from the result.
  • UNION ALL is similar to UNION, but a difference is that UNION ALL does not eliminate the duplicates, and it displays all the data/records.
  • INTERSECT operator only returns the record, which is common from both the SELECT statements.
  • The MINUS operator combines the result of two statements and returns only those that are present in the first SELECT query statement.
Free Courses by top Scaler instructors
certificate icon
Certificates
SQL Tutorial
This program includes modules that cover the basics to advance constructs of SQL Tutorial. The highly interactive and curated modules are designed to help you become a master of this language.'
If you’re a learning enthusiast, this is for you.
Module Certificate
Criteria
Upon successful completion of all the modules in the hub, you will be eligible for a certificate.
You need to sign in, in the beginning, to track your progress and get your certificate.