René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

select [Oracle SQL]

[with...]
select
  /*+ hint */
  col_1, 
  col_2,
  ...
from
  table_1 join table_2 on q=r
[where 
  x=y  and
  b=c]
[hierarchical-clause]
[group_by]
[having
  e=f]
[model-clause]
[set-operator]
[order by f]

Set Operators

select  col_1, col_2, col_3, ... col_n from table_1 set operator
select  col_1, col_2, col_3, ... col_n from table_2 set operator
...
...
select  col_1, col_2, col_3, ... col_n from table_n;
The four set operators union, union all, intersect and minus allow to serially combine more than one select statements. Although more than one select statement will then be present, only one result set is then returned.
If the select statements vary in their numbers of returned columns, Oracle report an ORA-01789: query block has incorrect number of result columns.
For the demonstration of set operators, the following test tables are created:
create table table_1 (
  col_1   number,
  col_2   varchar2(10),
  col_3   date
);

create table table_2 (
  col_1   number,
  col_2   varchar2(10),
  col_3   date
);
Then, a few values are inserted:
alter session set nls_date_format='dd.mm.yyyy';

insert into table_1 values (  3, 'hello' , to_date('28.08.1970'));
insert into table_1 values ( 42, 'galaxy', to_date('01.01.2001'));
insert into table_1 values (100, 'bye'   , to_date('09.02.2004'));

insert into table_2 values (  3, 'bye'   , to_date('28.08.1970'));
insert into table_2 values ( 42, 'galaxy', to_date('01.01.2001'));
insert into table_2 values ( 60, 'bye'   , to_date('09.02.2004'));
insert into table_2 values (  3, 'hello' , to_date('05.05.2002'));

union all

union all selects all rows from all select statements:
select col_1, col_2, col_3 from table_1 union all
select col_1, col_2, col_3 from table_2;
As can be seen, all records of both tables are returned:
     COL_1 COL_2      COL_3
---------- ---------- ----------
         3 hello      28.08.1970
        42 galaxy     01.01.2001
       100 bye        09.02.2004
         3 bye        28.08.1970
        42 galaxy     01.01.2001
        60 bye        09.02.2004
         3 hello      05.05.2002

union

union all is very similar to union, however, it dismisses duplicate rows found across different select statements:
select col_1, col_2, col_3 from table_1 union
select col_1, col_2, col_3 from table_2;
The galaxy record is a duplicate. Hence, it is returned only once:
     COL_1 COL_2      COL_3
---------- ---------- ----------
         3 bye        28.08.1970
         3 hello      28.08.1970
         3 hello      05.05.2002
        42 galaxy     01.01.2001
        60 bye        09.02.2004
       100 bye        09.02.2004

intersect

intersect only returns the rows that are found in all select statements:
select col_1, col_2, col_3 from table_1 intersect
select col_1, col_2, col_3 from table_2;
Only the galaxy record is returned. It's the only record that is stored in both tables:
     COL_1 COL_2      COL_3
---------- ---------- ----------
        42 galaxy     01.01.2001

minus

minus returns all rows from the first select statements except those who are duplicated in a following select statement:
select col_1, col_2, col_3 from table_1 minus
select col_1, col_2, col_3 from table_2;
As the galaxy record is found in both tables, it is removed from the first table's record set:
     COL_1 COL_2      COL_3
---------- ---------- ----------
         3 hello      28.08.1970
       100 bye        09.02.2004

as of

... to be finished ... see Flashback query and Flashback query example 1.

versions between

... to be finished ... see Flashback version query

Order of select processing