Set Operators
- Based on SET Theory.
Emp 1 Table:
| EMPNO |
ENAME |
| 1 |
A |
| 2 |
B |
| 4 |
D |
| 5 |
E |
note: union takes "empno1" as a heading because it came first in select statement.
Union:
-
Union will combine the output of both the SELECT statements and the duplicates are suppressed.
Select empno1, ename from emp1
union
Select empno2, ename from emp2;
| empno1 |
ename |
| 1 |
A |
| 2 |
B |
| 3 |
C |
| 4 |
D |
| 5 |
E |
- Uses:
- Full outer join.
- If you have a large number of rows, it’s recommended to create a new table each year or quarter.
Union All:
- Union All will combine the output of both the SELECT statements and the
duplicate are not suppressed.
Select empno1, ename from emp1
union all
Select empno2, ename from emp2
order by 1;
| empno1 |
ename |
| 1 |
A |
| 1 |
A |
| 2 |
B |
| 2 |
B |
| 3 |
C |
| 4 |
D |
| 5 |
E |
Intersect:
- Intersect will return what is common in both the SELECT statements and the duplicates are suppressed.
Select empno1, ename from emp1
intersect
Select empno2, ename from emp2
order by 1;
Except
- Except will return what is present in the first SELECT statement, not present in the 2nd SELECT statement and the duplicates are suppressed.
Select empno1, ename from emp1
except
Select empno2, ename from emp2
order by 1;