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

 

 

Advertisements

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