SQL Set Operator

Set Operator 

Set Operator:-Set Operator are used to get the result from different queries.
Below are set operator in SQL
 1) union
 2) union all
 3) intersect
 4) minus

Constraint to use Set Operator :- There are below constraint to use Set Operator
               1) All queries should have same number of columns
               2) All columns datatype should be sequentially matched
               3) Order by clause can be used only with last query

1) Union :-Union displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order
           (ascending by default), without ignoring the NULL values.

select empno,ename,job,deptno from emp where deptno=10
union
select empno,ename,job,deptno from emp where deptno in (10,20)
order by empno;








2) Union all:-Union all gives the result set without removing duplicate and sorting the data.

select empno,ename,job,deptno from emp where deptno=10
union all
select empno,ename,job,deptno from emp where deptno in (10,20)
order by empno;





3) Intersect:-Intersect displays the common rows from both the all queries, with no duplicates and in sorted order (ascending by default).

select empno,ename,job,deptno from emp where deptno =20
intersect
select empno,ename,job,deptno from emp
order by empno;






4) Minus :-Minus operator displays the rows which are present in the first query but not present in the second query, with no duplicates and data arranged in ascending order by default.

select empno,ename,job,deptno from emp
minus
select empno,ename,job,deptno from emp where deptno in (20,30)
order by empno;




select empno,ename,job,deptno from emp where deptno in (20,30)
minus
select empno,ename,job,deptno from emp
order by empno;