Set Operators
- Set operators basically combine the result of two queries into one
- These queries are known as compound queries.
These are the set operater.
UNION: Returns all rows from either queries; no duplicate rows.
UNION ALL: Returns all rows from either query, including duplicates.
INTERSETCT: Returns distinct rows that are returned by both queries.
MINUS: Returns distinct rows that are returned by the first query but not returned by the second.
Example
Query1 Select ename from emp where dept_id=40;
This query returns result
Alex
Peter
John
Richa
Query2 Select ename from emp where sal>=4000;
This query returns result
Aled
Peter
John
Chrish
Union: If we'll write Query1 Union Query2 Like
(Select ename from emp where dept_id=40) Union ( Select ename from emp where sal>=4000)
This will return result
Alex
Peter
John
Richa
Aled
Chrish
UNION ALL: If we'll write Query1 Union All Query2 Like
(Select ename from emp where dept_id=40) Union All ( Select ename from emp where sal>=4000)
This will return
Alex
Peter
John
Richa
Aled
Peter
John
Chrish
INTERSETCT: If we'll write Query1 INTERSETCT Query2 Like
(Select ename from emp where dept_id=40) INTERSETCT ( Select ename from emp where sal>=4000)
This will return
Peter
John
MINUS:If we'll write Query1 MINUS All Query2 Like
(Select ename from emp where dept_id=40) MINUS ( Select ename from emp where sal>=4000)
This will return
Alex
Richa