SET OPERATORS

SET OPERATORS

Set operators are used to join the results of two (or more) SELECT statements.

TYPES::

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

Condition to Use SET Operators::

1.Number of Columns Should be same
2.Order By Should be Place at Last of the Query
3.The data types Should Be same
4.First Query Alias Name will take for the Entier Result SET.

1.UNION::

When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values.

Example::

SQL> SELECT 1 UNION_RESULT FROM Dual
2  UNION
3  SELECT 2 FROM DUAL
4  UNION
5  SELECT 3 FROM Dual
6  UNION
7  SELECT 3 FROM Dual;

UNION_RESULT
------------
1
2 [Removed Duplicate Records]
3

2.UNION ALL::

UNION and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data.

Example::

SQL> SELECT 1 UNION_ALL FROM Dual
2  UNION ALL
3  SELECT 1 FROM DUAL
4  UNION ALL
5  SELECT 2 FROM Dual;

UNION_ALL
----------
1
1 [With out Remove Duplicates]
2

3.INTERSECT::

Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates.

Example::

SQL> SELECT ID,NAME FROM TEST
2  INTERSECT
3  SELECT ID,NAME FROM TESTING;

ID NAME
---------- -------------
1 Vino
2 Mathi
3 Xavi

4.MINUS::

     MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.

Example::

SQL> SELECT * from Test;

ID NAME
---------- -------------------
1 Vino
2 Mathi
3 Xavi

SQL> SELECT * from Testing;

ID NAME
---------- -------------------
4 xx
5 YY
6 ZZ

SQL> SELECT ID from TEST
2  MINUS
3  SELECT ID FROM TESTING;

ID
----------
1
2
3

SQL> SELECT ID,NAME from TEST
2  MINUS
3  SELECT ID,NAME FROM TESTING;

ID NAME
---------- ----------------------
1 Vino
2 Mathi
3 Xavi

-------END-------