written by db-nemec.com 2004
revised 2.1.2005
Let start with two set of numbers: prime numbers (table PRIME with all
prime numbers below 20) and odd number (table ODD with all odd numbers below
20).
The question which number is prime AND odd can be resolved with an inner
join as follows:
SQL> select
prime.x, odd.x
2
from prime INNER JOIN odd ON prime.x = odd.x
3
order by prime.x
4 ;
X X
---------- ----------
3
3
5 5
7 7
11 11
13 13
17 17
19 19
There is also an alternative syntax for the join condition (to be used
if the join column has the same name in both tables)
select x
from prime INNER JOIN
odd USING (x)
order by x
For the similar question which number is prime OR odd the FULL OUTER
JOIN (new 9i feature) must be used.
SQL> select
prime.x, odd.x, nvl(prime.x,odd.x)
2
from prime FULL OUTER JOIN odd ON prime.x = odd.x
3
order by nvl(prime.x,odd.x)
4 ;
X X NVL(PRIME.X,ODD.X)
---------- ----------
------------------
1 1
2 2
3 3 3
5 5
5
7 7
7
9 9
11 11
11
13 13
13
15 15
17 17
17
19 19
19
Again the alternative syntax with USING(x) can by used.
Note that for full outer join you must use UNION ALL of an outer join
and a NOT EXISTS select prior to 9i. That is also the way how this stuff is
resolved internally. See explain plan:
SQL> EXPLAIN PLAN
2
FOR
3
select x
4
from prime FULL OUTER JOIN odd USING (x)
5
order by x
6 ;
EXPLAIN PLAN ausgeführt.
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY)
2 ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 |
32 (13)|
| 1 |
SORT ORDER BY | | 10 | 130 | 32
(13)|
| 2 |
VIEW | | 10 | 130 | |
| 3 |
UNION-ALL | | | | |
|* 4 |
HASH JOIN OUTER | | 8 | 32 | 16
(13)|
| 5 |
TABLE ACCESS FULL| PRIME
| 8 | 16 |
8 (13)|
| 6 |
TABLE ACCESS FULL| ODD
| 10 | 20 | 8 (13)|
|* 7 |
HASH JOIN ANTI | | 2 | 8 | 16
(13)|
| 8 |
TABLE ACCESS FULL| ODD
| 10 | 20 |
8 (13)|
| 9 |
TABLE ACCESS FULL| PRIME
| 8 | 16 |
8 (13)|
--------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
4 -
access("PRIME"."X"="ODD"."X"(+))
7 -
access("PRIME"."X"="ODD"."X")
Note that the NOT EXISTS part is
performed as HASH ANTI-JOIN.
To check if a odd number IS a prime number use simple an outer join
(outer join all odd numbers to the prime table, if you match the answer is yes,
if not (outer join don’t find corresponding record) the answer is no.
You have two possibilities LEFT and RIGTH outer join – to get the same
result you must switch the tables.
A left outer join
B -- is the same as
B right outer join
A -- and corresponds to syntax
FROM A,B WHERE a.x =
b.x(+)
SQL> select odd.x,
decode(prime.x,NULL,'N','Y') as is_prime
2
from odd LEFT OUTER JOIN prime ON prime.x = odd.x
3
--- left outer i.e. the whole left table along with the matches from the
right table
4
order by odd.x;
X I
---------- -
1 N
3 Y
5 Y
7 Y
9 N
11 Y
13 Y
15 N
17 Y
19 Y
10 rows selected.
--- using RIGHT OUTER
join ...
select odd.x, decode(prime.x,NULL,'N','Y') as is_prime
from prime RIGHT OUTER JOIN odd ON prime.x = odd.x
order by odd.x;
---
--- or former syntax ...
---
select odd.x, decode(prime.x,NULL,'N','Y') as is_prime
from odd, prime where prime.x(+) = odd.x
order by odd.x;
--
-- gives the same
result
To demonstrate a join of three tables we add an other table with
fibonacci numbers (table fib)
--- all prime AND odd
AND fibonacci numbers
SQL> select
prime.x, odd.x, fib.x
2
from prime INNER JOIN
odd ON prime.x = odd.x INNER JOIN fib
3
ON odd.x = fib.x
4
order by prime.x
5 ;
X X X
---------- ----------
----------
1 1 1
3 3 3
5 5 5
13 13 13
you may use an alternative syntax with USING(x) ..
select x
from prime INNER JOIN
odd USING (x) INNER JOIN fib USING (x)
order by x
or use parenthesis this way ..
--- parenthesis
control the order of join
select x
from (prime INNER
JOIN odd USING (x)) INNER JOIN fib USING (x) -- default
order by x
or that way ..
select x
from prime INNER JOIN
(odd INNER JOIN fib USING (x)) USING (x)
order by x
The last two options seems to control the join order, but luckily they
are NOT! As the order of inner join is not critical the result, Oracle
optimiser does his work ignoring the parentheses (observed in 9.2). You may use
an ORDERED hint to force the join order.
Caution: be careful with a combination of inner and outer join – here
are the parenthesis meaningful an may have influence on the result of the
query.