Team Fly 

Page 74

CRITICAL SKILL 2.11
Use Set Operators: Union, Intersect, Minus

One of the nice things about a relational database is that SQL queries act upon sets of data versus a single row of data. Oracle provides us with a series of set functions that can be used to join sets together, for example. The set functions will be discussed in the next few sections using two single column tables: table x and table y. Before proceeding to the discussion on the set functions, let's first take a look at the contents of these tables.

Table x:

SQL> select * from x;

COL
---
1
2
3
4
5
6

6 rows selected.

Table y:

SQL> select * from y;

COL
---
5
6
7

3 rows selected.

union

When you use this operator in SQL*Plus, it returns all the rows in both tables without any duplicates. This is done by Oracle with a sort operation. In the preceding table listings, both tables have columns with values of 5 and 6. A closer look at the union query and resulting output is shown here:

SQL> select * from x
  2  union
  3  select * from y;
Team Fly 
0093-CRITICAL SKILL 2.11 Use Set Operators: Union, Intersect, Minus