Learn Set Operators In SQL (UNION, UNION ALL, INTERSECT, EXCEPT)

Summary: In this tutorial, you will learn about Set Operators in SQL such as UNION, UNION ALL, INTERSECT, EXCEPT With Practical Example. Let’s understand the set operators in SQL

Introduction to SQL Set Operators

Set Operators in SQL are basically used to combine the same type of data from two or more tables. In another word you can say that SQL Set operators are used to join the results of two (or more) SELECT statements. Therefore, although more than one select statement will then be present, only one result set is coming back or returned.

Venn Diagram Presentation:-

Set Operators In SQL
The UNION set operator in SQL returns the combined results of the two SELECT statements. primarily, it removes the duplicates from the results means that only one row will be listed for every duplicated result. To counter this behavior, you can use the UNION ALL set operator that retains the duplicates in the final result. Furthermore, In SQL INTERSECT lists only records that are common to both the SELECT queries. when in the SQL Server the MINUS set operator removes the 2nd query’s results from the output if they are also found in the 1st query’s results. Thus, INTERSECT and MINUS set operations in SQL to produce unduplicated results.

Types of Set Operators In SQL

There are four basically types of SQL operators, such as:

  • Union
  • Union all
  • Intersect
  • Minus

Here is an abstract table for whatever we’ll be learning during this set operators SQL article.

SQL Set Operator Function/Returns
Union Combines distinct results of two or more SELECT statements, without duplicates.
Union All Combines all results of two or more SELECT statements, including all duplicates.
Intersect Returns only the common records obtained the data from two or more SELECT statements.
Minus Returns only those records which are exclusive to the first table (i.e. no matching to each other)

Syntax of Set Operators

Parameters: The different parameters utilized in the syntax are:-

  • SET OPERATOR: Here, you can mention the type of set operation such as Union, Union all, Intersect, Minus which you want to perform.
  • Column_Name: Mention the column name on which you wish to perform the set operation & want in the result set.
  • FROM Table2: Mention the first table name from which the column has to be fetched
  • FROM Table2: Mention the second table name from which the column has to be fetched

Above all mentioned parameters, are mandatory. You can use WHERE GROUP BY and HAVING clauses as per your requirements.

For Demonstration Purpose: A schema for the discussed tables is given below as follows:

Schema Table

Table1: Dell_Customers
CustomersID Name City
11 Aditya Shukla Lucknow
12 Sumit Yadav Kanpur
13 Anita Singh Jaipur
14 Satendra Kumar Lucknow
15 Pushpendra Verma Noida
16 Saumya Tripathi Gr. Noida
17 Shivaditya Shukla Kolkata
18 Jaishankar Tripathi Ghaziabad
19 Manmohan Kumar Lucknow
20 Tushar Dixit Surat
Table2: HP_Customers
CustomersID Name City
11 Abhishek Thakur Jaipur
12 Tushar Dixit Kanpur
13 Shivaditya Shukla Mumbai
14 Pankaj Garg Noida
15 Saumya Tripathi Gr. Noida
16 Ram Kumar Jhasi
17 Sunita Yadav Kanpur
18 Mohit Chauhan Kolkata
19 Pushpendra Verma Noida
20 Kabir Singh Nainital

Practical Examples of Set Operators In SQL

1) Union Set Operator

The UNION set operator in SQL is used when you need to combine the results obtained from two or more SELECT statements. The given below example illustrates the use of the SQL UNION Operator.

Example: Let us find the name of all the customers


set operators in sql: UNION Operator Example

.
Important Note: Unique selects only distinct values. It will produce issues after we have customers with identical names but different countries or cities.
You will notice that there are two customers named Shivaditya Shukla, one is from Mumbai and another one from Kolkata. But the SQL Union operator returned only one customer name. so as to resolve the above-mentioned problem, we can use the SQL UNION ALL operator.

2) Union All Set Operator

The SQL UNION set operator is used to combining all the results obtained from two or more SELECT statements. Therefore, unlike the Union operator, it considers duplicate values & includes them in the final result.

Here is an example to illustrate the How to use of UNION ALL Operator.
Example:Find the names of all the customers from table Dell_Customers and HP_Customers.


set operators in sql: Union All Set Operator Example

.
Important Note: Here, you will notice that there are two distinct customers named Shivaditya Shukla, one is from Kolkata and another one from Mumbai. Union ALL operator took both into consideration and returned Shivaditya Shukla twice. The same is the case for another name but the city is the same with an identical name.

3) Intersect Set Operator

The SQL intersect set operator is used to combine all the results of two SELECT statements. But it returns only those records that are common to both the SELECT statements.
The given below is an example to illustrate the use of the INTERSECT Operator in SQL.

Example: Find the details of customers who Purchase HP and DELL laptops.


Intersect Set Operator Example

.
Important Note: If the INTERSECT operator is not supported in your MYSQL databases. then you can use the IN or EXIST IN clause for performing similar operations.

4) Minus Set Operator

The MINUS set operator in SQL is used to combine all the results of two or more SELECT statements. Furthermore, but they return only those records that are present exclusively in the first table.
Therefore, The given below is an example to illustrate the use of the EXCEPT Operator.
Example: Find the details of customers who shopped only DELL laptops but not HP laptops.


Minus Set Operator Example

About Author

admin