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
Set Operations