SET OPERATORS

SET OPERATORS

UNION
UNION ALL
INTERSECT
MINUS

SQL> SELECT * FROM tab1;

       SNO SNAME
---------- ----------
         5 lathika
         6 karthika
         1 Parthi
         2 karthi
         3 kamal
         4 siva

6 rows selected.

SQL> SELECT * FROM tab2;

       SNO SNAME
---------- ----------
         1 siva
         2 ravi
         3 kavi
         4 santhosh
         5 santhosh
UNION 
-- Conditions 
Colomn and Data Type must same 
combine two tables and remove the duplicates 

SQL> SELECT sno FROM tab1 UNION SELECT sno FROM tab2;

       SNO
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

UNION ALL 
--To include the duplicates 

SQL> SELECT sno FROM tab1 UNION ALL SELECT sno FROM tab2;

       SNO
----------
         5
         6
         1
         2
         3
         4
         1
         2
         3
         4
         5

11 rows selected.

INTERSECT

Its display common data from both table.

SQL> SELECT sno FROM tab1 INTERSECT SELECT sno FROM tab2;

       SNO
----------
         1
         2
         3
         4
         5

MINUS

To display the values in the first table not in second table.

SQL> SELECT sno FROM tab1 MINUS SELECT sno FROM tab2;

       SNO
----------
         6

Note:

-- SET OPERATORS used in more than 2 SELECT statement.
-- Top to bottom will be executed.
-- SET OPERATORS when used common data type will matched otherwise error.
-- Multiple number of columns are used in set operators condition is no of columns is matching otherwise its thrown a error.
-- order by is used in last SELECT statement. (if its used in first SELECT statement its wrong);