Set Operator

Set operators with examples

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

 

 

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