/    /  DBMS – Set Operations

Set Operations

 

The set operators are : 

Union: The Union operation is used to merge the result of two or more SQL SELECT queries. This operation removes the duplicate rows from the result set.

Syntax

SELECT column_name FROM table1 

UNION 

SELECT column_name FROM table2; 

Intersect : This operation is used to merge two SELECT statements. It returns the same rows from both the SELECT statements. The number of data types and columns must be the same.

Syntax:

SELECT column_name FROM table1 

INTERSECT 

SELECT column_name FROM table2; 

 

 

 

Except: Takes the data from the first result set, but not in the second result set (i.e. no matching to each other.

Syntax:

SELECT ID, Name, Gender, Department FROM TableA

EXCEPT

SELECT ID, Name, Gender, Department FROM TableB

 

 Let us consider the following example: 

 

R4 :  FACULTY-1

———————————————————— 

FNo FName              DNo Qual           Salary

————————————————————

22 Ria  21 Ph.D. 35000

24 Priya  22 MTech 30000

25 Sia  22 MTech 42000

27 Maggie 23 MTech 28000

30 Alex 23 MTech 32000

33 Ben 24 Ph.D.          33000

35 Becca  24 Ph.D.          32000

37 Justin  25 MTech 26000

39 Pat  25 MTech 24000

40 Jack 25 MTech 34000

————————————————————

 

Example-1 : Find all FNames who are working in Dno 22 or in DNO 24.

(Select FName

   From  FACULTY-1

   Where DNo = 22)

   union

   (Select FName

   From  FACULTY-1

   Where DNo = 24) ;

 

   Output :  FNames

 Priya 

   Sia 

Maggie

Alex 

 

Example-48 : Find all FNames who are working in Dno 23 and DNO 25.

  (Select FName

   From  FACULTY-1

   Where DNo = 23)

   intersect

   (Select FName 

   From  FACULTY-1 

   Where DNo = 25) ;

 

   Output :  FNames

 N I L

Reference

Set Operations