SQL - SET OPERATORS

SEToperators are special type of operators which are used to combine the result of two queries.

FOUR TYPE SET OPERATORS:

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

It returns all distinct rows selected by either query

Select  * from tesdb01
Union
Select  * from tesdb02;
SQL> select * from tesdb01 union select * from tesdb02;

COURES_ID             NAME                 COURSE_NAME
----------    --------------------   --------------------
101            arun                 oracle_dba
102            babu                 java
102            bala                 java
103            akash                python
103            ram                  postgres
104            manoj                oracle_dba
105            sam                  testing
105            vicky                java

8 rows selected.
2. UNION ALL:

It returns all rows selected by either query

Select * from tesdb01
Union all
Select * from tesdb02;
SQL> select * from tesdb01 union all select * from tesdb02;

COURES_ID           NAME                  COURSE_NAME
----------    --------------------     --------------------
101           arun                    oracle_dba
102           bala                    java
103           akash                   python
104           manoj                   oracle_dba
105           vicky                   java
101           arun                    oracle_dba
102           babu                    java
103           ram                     postgres
104           manoj                   oracle_dba
105           sam                     testing

10 rows selected.
3. INTERSECT:

It returns all distinct rows selected by both query

select * from tesdb01
Intersect
Select * from tesdb02;
SQL> select * from tesdb01 intersect select * from tesdb02;

COURES_ID           NAME              COURSE_NAME
----------    -------------------- --------------------
    101           arun              oracle_dba
    104           manoj             oracle_dba
4.MINUS:

It returns all distinct rows selected by first query only.

Select * from tesdb01
Minus
Select * From tesd02
SQL> select * from tesdb01 minus select * from tesdb02;

COURES_ID	NAME		COURSE_NAME
---------	----------	-------------
102		bala		java
103		akash		python
105		vicky		java