Set Operator in SQL

Set Operator:-
              Set Operators combine the result of two or more component queries into a single result. Queries containing set operators are called compound queries.

1. UNION
2. MINUS
3. UNION ALL
4. INTERSECT

Table EMP

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000

Table EMP_DETAIL

ID    NAME    SALARY
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000


UNION:-
        All distinct rows selected by either query. In UNION operator data is sorted in ascending order according to first column of first query.

Example:-
        SELECT ID,NAME,SALARY
        FROM EMP
        union
        SELECT ID,NAME,SALARY
        FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
2    NAVAL    10000
3    SEEMA    50000
4    SUSHIL    90000
4    ANKIT    60000
5   AYUSH    30000


MINUS:-
        All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.

Example:-
        SELECT ID,NAME,SALARY
        FROM EMP
        minus
        SELECT ID,NAME,SALARY
        FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
2    SAJAL    20000
4    SUSHIL    90000
       
UNION ALL:-
            All rows selected by either queries, including all duplicates.

Example:-
        SELECT ID,NAME,SALARY
        FROM EMP
        union all
        SELECT ID,NAME,SALARY
        FROM EMP_DETAIL;
       
Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000
           
INTERSECT:-
            All distinct rows selected by both queries.
           
Example:-
        SELECT ID,NAME,SALARY
        FROM EMP
        intersect
        SELECT ID,NAME,SALARY
        FROM EMP_DETAIL;
               
Output:-

ID    NAME    SALARY
1    AMAL    80000
3    SEEMA    50000