SQL Union, Union All, Intersect and Minus

SQL UNION, UNION ALL, INTERSECT and MINUS are set operations to help you to get the required data from multiple tables. It is basically application of Set Theory formulas on the rows returned by multiple SELECT Statements to get collective rows as single output.

Condition for SQL Union, Union All, Intersect and Minus

• To get data from multiple tables using these set operators, all the SELECT statements should have the same number of columns
• The data type of columns in all the SELECT the clauses must be same.
• If the data types of columns of different SELECT clauses are not exactly same then they should be convertible into the data types of the columns of the first SELECT Clause.

Syntax for SQL Union, Union All, Intersect and Minus queries

SELECT column name[s] FROM table1

UNION|UNION ALL|INTERSECT|MINUS

SELECT column name[s] FROM table1

You can perform the set operations on more than two SELECT statements with conditions mentioned above.

Examples of SQL Union, Union All, Intersect and Minus Set Operators

The tables used in the following examples are Students and StudentFee. Both the tables have a common column stuID. All the following set operator queries display stuID as output. The students table has 10 records and StudentFee table has 8 records.

Union

The Union set operator is same as in the Set Theory. It returns the union of output of the select statements combined with keyword UNION. It means that if both the SELECT statements have common values they are eliminated in the final result. The column headers are taken from the first SELECT Statement.

Example

select stuID from students
Union
select stuID from studentFee

Output

In the output all the stuID column values from two SELECT Statements are displayed in the final output. It excludes any duplicates generated by the two SELECT Statements. This query returns 13 rows 10 from students and 3 unique from StudentFee.

Union All

The Union All returns the union of the outputs of the select statements combined with keyword UNION ALL. If the individual SELECT statements have common values they are included in the final result.

Example

select stuID from students
Union All
select stuID from studentFee

Output

This statement returns 18 rows in total. It includes 10 rows from students table and 8 rows from studentFee table. Duplicate rows are not eliminated.

Intersect

The INTERSECT set operator returns only the common rows of the outputs of the select statements combined with keyword INTERSECT. The common rows from the outputs of individual SELECT statements are included in the final result.

Example

select stuID from students
Intersect
select stuID from studentFee

Output

This statement returns 5 rows in total. From 10 rows from students table and 8 from studentFee table the common rows are displayed.

Minus

The MINUS set operator returns rows from the output of first select excluding those present in the output of the second SELECT statement.

Example

select stuID from students
Minus
select stuID from studentFee

Output

This statement returns 4 rows in total. The rows returned are from the first SELECT statement excluding those present in output of the second SELECT statement.

Where Clause with Set Operators

You can filter rows by defining WHERE clause in individual SELECT Statements.

Example

select stuid from students where stuname like'j%'
union
select stuid from studentfee where feepaid=200

Output

The SELECT statements return the rows after the individual WHERE clause condition and UNION operator joins these filtered rows.

Order By Clause with Set Operators

You cannot add the ORDER BY clause with individual SELECT statements. ORDER BY clause can only be added to the last SELECT statements.

Example

select stuid from students 
union all
select stuid from studentfee order by stuid

Output

The SELECT statements return the rows. After the rows are combined with UNION operator the final combined rows are ordered.

Example Tables- Structure and Insert Statements

create table students
(stuID  number(4),
stuName varchar2(30),
stuClass varchar2(10)
)
insert into students values (101,'John','MSc-I');
insert into students values (102,'Cena','MSc-I');
insert into students values (103,'Heran','MSc-II');
insert into students values (104,'Bella','MSc-I');
insert into students values (105,'Bianca','MSc-II');
insert into students values (106,'Jeo','MSc-II');
insert into students values (107,'Katy','MSc-I');
insert into students values (108,'Lena','MSc-II');
insert into students values (109,'Carrey','MSc-I');
insert into students values (110,'Zoe','MSc-I');

create table studentfee
(stuID  number(4),
feeDate date,
feePaid number(8,2)
)
insert into studentfee values (101,'09-Mar-20',200);
insert into studentfee values (102,'09-Mar-20',200);
insert into studentfee values (103,'09-Mar-20',300);
insert into studentfee values (107,'09-Mar-20',200);
insert into studentfee values (110,'09-Mar-20',200);
insert into studentfee values (111,'09-Mar-20',200);
insert into studentfee values (112,'09-Mar-20',300);
insert into studentfee values (113,'09-Mar-20',200);

admin