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

Advertisements