SET Operators in SQL | UNION, UNION ALL, INTERSECT, MINUS

SET Operators in SQL | UNION, UNION ALL, INTERSECT, MINUS

Set operators are specialized types of operators that are used to combine the results of two or more queries. These operators are used to extract the desired results from the table data that is stored in the table. The set operators and SQL joins have a similar look, but they differ significantly. SQL joins combine the columns from different tables, whereas SQL operators combine rows from different queries. Though both concepts are used to combine data from multiple tables, joins combine columns from separate tables, whereas set operations combine rows from separate tables.

When using set operators in SQL, you must follow certain rules. The following are some of these:

• The number of columns in the both the SELECT statement must be the same.
• The order of columns must be in the same order.
• The selected columns must have the same data type.

Types of Set Operations 
There are different types of set operators that are mentioned below:

• UNION
• UNION ALL
• INTERSECT
• MINUS

To understand the above set operators lets consider a two tables. We will perform all set operation on above mentioned table.

table1		table2
A		A
B		B
C		E
D		F

1. UNION 

• UNION combines & return the distinct result of two or more SELECT statements.
• Output sorted by default
• Not desired from performance aspect since this involves duplicate removal & sorting.

Syntax:

SELECT * FROM table1

UNION

SELECT * FROM table2;

Output:

output

A

B

C

D

E

F

2. UNIONALL 

• UNIONALL return all the rows from two or more SELECT statements.
• Output not sorted by default
• Wont remove duplicate values
• Desired from performance aspect since no duplicate removal & sorting are performed.

Syntax:

SELECT * FROM table1

UNIONALL

SELECT * FROM table2;

Output:

output

A

B

C

D

A

B

E

F

3. INTERSECT 

• INTERSECT return all the common rows from two or more SELECT statements.
• Output is sorted by default.
• Remove the duplicate records.

Syntax:

SELECT * FROM table1

INTERSECT

SELECT * FROM table2;

Output:

output

A

B

4. MINUS 

• MINUS return all the record from one table excluding records from other table.
• Output is sorted by default.
• Remove the duplicate records.

Syntax:

SELECT * FROM table1

MINUS

SELECT * FROM table2;

SELECT * FROM table2

MINUS

SELECT * FROM table1;

Output:

output

C

D

output

E

F

Share This:

Facebook Twitter Pinterest Linkedin Whatsapp Whatsapp