René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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 Operatorsselect 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 allunion 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 unionunion 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 intersectintersect 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 minusminus 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
|