You are here

SQL: SET OPERATORS

set operators

SET OPERATORS

In this post we discuss about set operators with example for better understanding. In SQL there three set operators union, intersect, except.

  • Before discussing about set operators we have to know some rules for set operators:
  • Column alias must be expression in the first query.
  • Each select statement must have the same number of columns and columns must have the same data type.
  • Each select statement must contains the columns in the same order.

UNION

  • UNION returns a result set of distinct rows combined from all statement.
  • It remove duplicates during query processing.

Syntax:
Select column_name(s) from table1
UNION
Select column_name(s) from table2

UNION ALL

  • The UNION ALL operator unifies the result of the two input queries, but doesn’t try to remove duplicate during query processing.

Syntax:
Select column_name(s) from table1
UNION
Select column_name(s) from table2

Difference between UNION and UNION ALL

UNION UNION ALL
It does not return duplicate records. It returns a duplicate record.
It is slower than UNION ALL. It is faster than UNION but may look slow because it return more data which take more time to travel via the network.
It cannot work with column that has a text data type. It works with all data type.
It use distinct sort. It does not uses a distinct sort.

INTERSECT

  • INTERSECT only returns distinct rows that appear in both result set.

Syntax:
Select column_name(s) from table1
INTERSECT
Select column_name(s) from table2

EXCEPT

  • It returns only distinct rows that appear in the first set but not in the second set.
  • Orders in which sets are specified matters.

Syntax:
Select column_name(s) from table1
EXCEPT
Select column_name(s) from table2

Example

Table : emp1

Eid ename
 1 Riya
2 Bindiya
3 Savan
4 Diya

Table: emp2

eid ename
1 Diya
2 Gopi
3 Bindiya
4 Hema

Combine the result returned by the two queries to create a list of all employee names without including duplicates.
select ename from emp1
UNION
select ename from emp2;

output:

ename
Bindiya
Diya
Hema
Riya
Savan

Combine the result returned by the two queries to create a list of all employee names including duplicates.
select ename from emp1
UNION ALL
select ename from emp2;

output:

ename
Riya
Bindiya
Savan
Diya
Diya
Gopi
Bindiya
Hema

Retrieve all employee that includes in both table.
select ename from emp1
INTERSECT
select ename from emp2;
output:

ename
Bindiya
Diya

Retrieve all employee that includes in the first table but not in the second table.
select ename from emp1
EXCEPT
select ename from emp2;
output:

ename
Riya
Savan

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top