Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 22, 2002 - 7:25 am UTC

Last updated: May 17, 2019 - 9:10 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hi Tom,
I know in oracle9i we have the cross join and full outer join. Do they exist for 8i if so can you point me to the documentation. If they don't can you give an example/breakdown of how to rewrite in 8i.

Thanks in advance,


and Tom said...

to join in 8i, just use a where clause:

select *
from emp, dept
where emp.deptno = dept.deptno;

in 9i and up, you could

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP inner join SCOTT.DEPT
on emp.deptno = dept.deptno


to outer join 8i, use a (+)

select * from emp, dept where emp.deptno = dept.deptno(+)

is the same as the 9i syntax of:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP LEFT outer join SCOTT.DEPT
on emp.deptno = dept.deptno

whereas

select * from emp, dept where emp.deptno(+) = dept.deptno

is the same as:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP RIGHT outer join SCOTT.DEPT
on emp.deptno = dept.deptno


In 8i, a full outer join would be:

select * from emp, dept where emp.deptno = dept.deptno(+)
UNION ALL
select * from emp, dept where emp.deptno(+) = dept.deptno AND emp.deptno is null;

versus the 9i syntax (which does the same amount of work -- no magic there)

select *
from SCOTT.EMP FULL outer join SCOTT.DEPT
on emp.deptno = dept.deptno





Rating

  (105 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

join example, new vs old syntax

A reader, November 22, 2002 - 8:50 am UTC

Tom excellent examples thank you very much.
One last thing I wanted to confirm cross join is the same as a cartesian correct.

Tom Kyte
November 22, 2002 - 10:11 am UTC

I don't know what a "cross" join is myself -- not familar with that particular terminology.

but it probably means "cross product" which would put it in a class with the cartesian product - yes.

Why

Vladimir, November 22, 2002 - 2:18 pm UTC

Why did they come up with this confusing syntax?
How would I interpret
select ename, dname, emp.deptno, dept.deptno
from SCOTT.DEPT LEFT outer join SCOTT.EMP
on emp.deptno = dept.deptno
?
How would I join several tables?

Tom Kyte
November 22, 2002 - 2:58 pm UTC

from (dept left out join emp on emp.deptno = dept.deptno ) left outer join T on .....


ANSI came up with the syntax, not us. We are just conforming to it.

Where can I find these examples in 9i Manuals

Arun Gupta, November 22, 2002 - 9:10 pm UTC

Tom
I read an article about the ANSI syntax and how Oracle conforms to it in 9i. However, the article was not exhaustive. Please let me know where can I find more details in 9i manuals.

Cross Product

Jignesh, May 02, 2003 - 4:52 am UTC

Hi Tom,
Recently i have cleared my first ocp paper 1z0001. Of which i was asked the following question -->

Which of the following statements regarding column subqueries is true?

a. A pair wise comparison produces a cross product.
b. A non-pair wise comparison produces a cross product.
c. In a pair wise query, the values returned from the subquery are compared individually to the value in the outer query.
d. In a non-pair wise query, the values returned from the subquery are compared as a group to the values in the outer query.


I dint manage to answer this question. Could you please elaborate more on "Cross Product". I have searched the documentation but dont find any good explanation on that.


Tom Kyte
May 02, 2003 - 8:11 am UTC

I've no clue what a "column subquery" is in the first place. There is no such thing, it is a made up term.

I hate multiple guess tests...

this question -- in my humble opinion -- is none sensical (and none too relevant)

a cross product is a cartesian join -- but how you get from a subquery (and a "column subquery" whatever the heck that is) to join semantics is beyond me.

Cross join Algorithm

Asim Naveed, September 23, 2003 - 6:48 pm UTC

Hi

I am working in java language and java script,
as front end and
oracle as backend , its a web application.
There is a table "mytable" in my database which have 30
columns.


A user inputs a number (say N) in the internet browser,
then I have to display the data of
first N columns of the "mytable".

I issue the following SQL through ODBC regardless
of the value of N.

SELECT col1, col2, col3, .... col30 FROM mytable

Then when fetching rows, i use
getstring(1), getstring(2), ..... getstring(N).
to get only required no. of columns.

The list is displayed to the user, after that the user
click on NEXT button and then I have to cross join
all the displayed columns and display one long list.

One way I can do this is by using the following SQL.

SELECT * FROM
(SELECT col1 from mytable),
(SELECT col2 from mytable),
.
.
.

(SELECT colN from mytable)

BUT
I want to write a crossjoining algorithm in my front
end, and dont want to go back to database to get the
crossjoin.

Can you please point me to such algorithm,
any links, any books, any utility you have etc.

Thanks




Tom Kyte
September 23, 2003 - 8:14 pm UTC


and of what possible use would this cartesian product be???

if you want to do it on the client - go for it. it is just "code", you'll have to write it.

i've never seen the need for it, so i have no links, books, utilities.

Use of cartisian product

Asim Naveed, September 24, 2003 - 3:24 am UTC

Actually its a segmentation part of my application, which is
usually used in sales analysis. Note that the extra
dashes are just to make the output clear, <tab> dosent
seem to work properly.


SELECT * FROM mytable

sales===========Cost============Transaction Count
Ranges==========Ranges==========Ranges
-----===========----============-----------------
1-100-----------1-200-----------1-300
101-200---------201-400---------301-600
200-300---------401-600
301-400

Note that the 3 columns are not related in way, you can
just assume them independant arrays.

Then when the user see the above result, he presss NEXT,
then after cross joining all ranges, and getting no. of
Items sold for each segment, I will display the report like
this.



Segment No======Segment Description=============No. of Itms
----------======-------------------=============------------

1---------------Sales = 1-100---------------------------200
----------------Cost = 1-200
----------------Transactions = 1-300


2---------------Sales = 1-100---------------------------30
----------------Cost = 1-200
----------------Transactions = 301-600

3---------------Sales = 1-100---------------------------40
----------------Cost = 1-200
----------------Transactions = 1-300

4---------------Sales = 1-100---------------------------200
----------------Cost = 1-200
----------------Transactions = 1-300
.
.
.
.
.
all possible segments
.
.
.
.



Tom Kyte
September 24, 2003 - 9:39 am UTC

i'll say it one last time...

if you want to do this in the client -- go for it.

it is procedural code.
you own it.
you write it.

go for it.

Sorry, a little correction

Asim Naveed, September 24, 2003 - 3:31 am UTC

Please correct he above output,

Description of Segment no. 3 and 4 should be

Segment no.3
-------------
Sales = 1-100
Cost = 201-400
Transactions = 1-300

Segment no. 4
--------------
Sales = 1-100
Cost = 201-400
Transactions = 301-600

Also didnt oracle use a cross join algorithm when
se do
SELECT * FROM TABLEA, TABLEB

?

Thanks


Inner join v/s Equi join

Anil Pant, September 24, 2003 - 9:03 am UTC

Is inner join means a simple equi join ?
select a.col1, a.col2, b.col3
from t1 a, t2 b
where a.col1 = b.col3

is this known as inner join as Im bit confused ?If Im wrong pls correct

Tom Kyte
September 24, 2003 - 12:08 pm UTC

thats an inner join, yes.

Appologize

Asim Naveed, September 25, 2003 - 2:09 am UTC

I am extremly sorry , but you got me wrong, the
reason for reposting was NOT to ask you the question
again, BUT to answer your following question:-

"and of what possible use would this cartesian product
be???"

Thanks



A reader, November 14, 2003 - 4:06 pm UTC


difference between these queries

A reader, December 23, 2003 - 12:08 pm UTC

Hi

I am struggling understanding what does the AND after left outer join on does, consider these two queries

select ename, dname, b.deptno
from emp a, dept b
where a.deptno(+) = b.deptno
and b.deptno in (10, 40)

ENAME DNAME DEPTNO
---------- -------------- ----------
CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
OPERATIONS 40


select ename, dname, a.deptno
from dept a
left outer join emp b
on a.deptno = b.deptno
and a.deptno in (10, 40)

ENAME DNAME DEPTNO
---------- -------------- ----------
CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40


I am lost here, why deptno 20 and 30 appears?

Tom Kyte
December 23, 2003 - 4:46 pm UTC

it appears to be an interesting side effect of "ON"

you seem to want to be using "where"

Think of it this way:

scott@ORA9IR2> select ename, dname, a.deptno
2 from dept a
3 left outer join emp b
4 on a.deptno = b.deptno
5 and 1=0
6 /

ENAME DNAME DEPTNO
---------- -------------- ----------
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40


the left outer join syntax basically says "all rows in DEPT will be output". Period. All rows in DEPT will be output.

Now, each row in DEPT is joined to EMP based on the join condition -- which in this case is:

on a.deptno = b.deptno and a.deptno in (10, 40)

if NO matching rows for that join condition are to be found -- MAKE UP a row with null values and join to that.

That A is the "driving table" isn't material.

It is instructive in 9ir2 to use explain plan to see the actual predicates applied in each case:


scott@ORA9IR2> delete from plan_table;

6 rows deleted.

scott@ORA9IR2> explain plan for
2 select ename, dname, b.deptno
3 from emp b, dept a
4 where b.deptno(+) = a.deptno
5 and a.deptno in (10, 40)
6 /

Explained.

scott@ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN OUTER | | | | |
| 2 | SORT JOIN | | | | |
|* 3 | TABLE ACCESS FULL | DEPT | | | |
|* 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."DEPTNO"=10 OR "A"."DEPTNO"=40)
4 - access("B"."DEPTNO"(+)="A"."DEPTNO")
filter("B"."DEPTNO"(+)="A"."DEPTNO")

Note: rule based optimization

20 rows selected.

there it is clear that the filter (3) is applied AND THEN the rows are returned to join to emp (i've reversed your A/B in this first query to keep is consistent with the others!! A is DEPT and B is EMP


scott@ORA9IR2>
scott@ORA9IR2> delete from plan_table;

6 rows deleted.

scott@ORA9IR2> explain plan for
2 select ename, dname, a.deptno
3 from dept a
4 left outer join emp b
5 on a.deptno = b.deptno
6 and a.deptno in (10, 40)
7 /

Explained.

scott@ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 2378 | 166 |
| 1 | NESTED LOOPS OUTER | | 82 | 2378 | 166 |
| 2 | TABLE ACCESS FULL | DEPT | 82 | 1804 | 2 |
| 3 | VIEW | | 1 | 7 | 2 |
|* 4 | TABLE ACCESS FULL | EMP | 1 | 20 | 2 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("A"."DEPTNO"="B"."DEPTNO" AND ("A"."DEPTNO"=10 OR "
A"."DEPTNO"=40) AND ("B"."DEPTNO"=10 OR "B"."DEPTNO
"=40))

Note: cpu costing is off

19 rows selected.

Now, thats interesting -- the predicate it applied to a view of EMP, it is not really applied to DEPT at all -- this is the semantics of the ON clause in an OUTER JOIN, compare to an ON + WHERE:

scott@ORA9IR2> delete from plan_table;

6 rows deleted.

scott@ORA9IR2> explain plan for
2 select ename, dname, a.deptno
3 from dept a
4 left outer join emp b on a.deptno = b.deptno
5 where a.deptno in (10, 40)
6 /

Explained.

scott@ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 4 |
|* 1 | HASH JOIN OUTER | | 1 | 42 | 4 |
| 2 | INLIST ITERATOR | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 1 |
|* 4 | INDEX RANGE SCAN | PK_DEPT | 1 | | 2 |
| 5 | TABLE ACCESS FULL | EMP | 82 | 1640 | 2 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
4 - access("A"."DEPTNO"=10 OR "A"."DEPTNO"=40)

Note: cpu costing is off

19 rows selected.

scott@ORA9IR2>

Here we see the predicate is applied to the table DEPT, not EMP.






is this a bug or a feature?

A reader, December 23, 2003 - 5:24 pm UTC

Hi

You are correct, I want to use where, however I ended up using AND instead of WHERE because of Oracle University SQL course notes, it says:

Additional Conditions:
Applying Additional Conditions
You can apply additional conditions in the WHERE clause. The example shown performs a join on the EMPLOYEES and DEPARTMENTS tables, and, in addition, displays only employees with a manager ID equal to 149.

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149;


This example however uses a simple equijoin I am not sure the difference.

I tried very hard to find out the meaning of AND after on and only found this tiny note, nothing on Metalink nor the documentation (I am using 9.2.0.4 FYI)

I wonder, is this a bug or a feature? If it's a feature certainly it's not well documentated!

If you try

select ename, dname, a.deptno
from dept a
left outer join emp b
on a.deptno = b.deptno
and a.deptno in (10, 40)

instead of a.deptno = b.deptno you put b.deptno = b.deptno or deptno = b.deptno we all end up getting same result set. I checked the execution plan with dbms_xplan and I was puzzled too!

Last question, why you delete plan table after run an explain? I never do it and it works.... :-0


Tom Kyte
December 23, 2003 - 5:48 pm UTC

thats the difference -- a simple JOIN does not have the rule that "all rows from one of the tables are going to be sent down to the where clause no matter what"

I just delete to keep it "empty"

forgot to say

A reader, December 23, 2003 - 5:29 pm UTC

if you try the last part of my previous post I think this statement is not true, that is not longer the join condition...!

a.deptno = b.deptno and a.deptno in (10, 40)
b.deptno = b.deptno and a.deptno in (10, 40)
deptno = b.deptno and a.deptno in (10, 40)

all three returns same results

=====================================================
on a.deptno = b.deptno and a.deptno in (10, 40)
if NO matching rows for that join condition are to be found -- MAKE UP a row with null values and join to that.


Tom Kyte
December 23, 2003 - 5:52 pm UTC

huh? not sure where you are going there?



For Jignesh above

umesh, December 24, 2003 - 1:35 am UTC

The answer is 2) the non pair subquery retuerns a cross product
pair or ( multiple) column subquery is some thing like
select * from emp
where ( sal,comm) in ( select sal, comm from emp1 where ...)




a bug or a feature

A reader, December 24, 2003 - 2:49 am UTC

Hi

I think that's a bug, how can we fill a bug to Oracle Tom?

Tom Kyte
December 24, 2003 - 10:11 am UTC

it is not a bug -- i thought I talked thru what is happening there.


A left outer join B on ( any set of conditions )

will return ALL rows from A and whenever the "any set of conditions" is satisfied -- the rows from B. If no rows in B exist after evaluating "any set of conditions" -- then a NULL row is made up.

But - ALL rows from A will be returned. By the very definition of an outer join.

do not agree

A reader, December 24, 2003 - 2:00 pm UTC

It's true the very definition of OUTER join is return all rows even not matched however we are using a predicate to restrict that

select ename, dname, a.deptno
from dept a
left outer join emp b
on a.deptno = b.deptno
and a.deptno in (10, 40) --> restriction, clearly saying we only want deptno 10 and 40 from DEPT (and dont understand why this is applied to EMP ALWAYS?!)

Which is not taken into consideration at all by Oracle I mean who on earth want a result set which does not fit what he wants in the query? What is the use of this kind of query....?! In version before 9i we never get this kind of result sets!




Tom Kyte
December 24, 2003 - 2:28 pm UTC

this whole thing is begining to remind me of the people who get mad at the results of this query:

select * from emp where empno in ( select empno from dept );

but anyway....

you are NOT using a predicate. if you were, i would agree with you.

WHERE begins a predicate. If you used a predicate, you observe the behaviour you seem to expect.

ON is a join clause, a new thing in Oracle SQL with 9i. It restricts the rows returned from EMP -- not DEPT -- in the context of an outer join.

You are just quite simply not understanding the syntax here. ON != WHERE.

T1 left outer join T2 on ( any condition )

will by the very very very definition of an ANSI outer join return ALL ROWS of T1 -- all of them -- regardless of what "any condition" specifies since "any condition" is a join criteria for going from T1 to T2 -- it is not a predicate, it does not restrict the rows being returned.


The reason you never "got this before 9i" is because quite simply there was no syntax even remotely similar to this with the ON clause. It is new. In 8i and before everything was in the predicate. Now, everything ISN'T in the predicate.

Now however, you understand how it works. So, you can code correct sql.

The result set fits EXACTLY what you asked for -- you just didn't understand the semantics here.

and you know -- we don't "make up" this syntax. it is sort of the way it was told to us that it should be. This is the ANSI outer join syntax, not the Oracle outer join syntax.

can we find this explanation in any documentation?

A reader, December 24, 2003 - 5:33 pm UTC

Hi

Is this new outer join feature explained anywhere in the docs?

Tom Kyte
December 25, 2003 - 8:28 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625 http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2080356 <code>

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

how can we do this in 8i

A reader, December 25, 2003 - 10:13 am UTC

Hi

How can we get this result using 8i?

select ename, dname, a.deptno
from dept a
left outer join emp b
on a.deptno = b.deptno
and a.deptno in (10, 40)

ENAME DNAME DEPTNO
---------- -------------- ----------
CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40


Tom Kyte
December 25, 2003 - 10:45 am UTC



scott@ORA9IR1> select ename, dname, a.deptno
2 from dept a, (select * from emp where deptno in (10,40)) b
3 where a.deptno = b.deptno(+);

ENAME DNAME DEPTNO
---------- -------------- ----------
CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40

6 rows selected.


To the reader who "does not agree"

vc, December 25, 2003 - 9:14 pm UTC

As defined by the SQL'92 standard.

Assuming you have an outer join like this:

select * from t1 left join t2 on t1.x=t2.x and t2.y=15 where t2.z=17

't1' is the "preserved" table and 't2' is the "unpreserved" table.
Conceptually (not really), the process is like this:

1. A Cartesian join is built.
2. Each resulting row is scanned and, if the join condition tests TRUE, the row is kept; if the condition tests FALSE, then all the columns from t1 are kept, the columns from t2 are converted to NULLs and possible duplicates are eliminated.
3. The predicate from the WHERE clause is applied to the result set obtained in Step 2.

For non-outer (inner) joins, it does not matter where you specify the conditions to filter the Cartesian join. The result will be the same whether you give the conditions in the ON part or in the WHERE clause because there is no conceptual "preservation" in Step 2.

VC

which syntax is recommended

A reader, December 27, 2003 - 4:51 pm UTC

Hi

With new join syntaxes which one should we use? From the documentation Oracle seems to recommend the new syntax... any specific reason?

Tom Kyte
December 27, 2003 - 5:34 pm UTC

it is entirely up to you. 99.99% matter of choice.

I find it easier to read the new style, but, i've been doing the other one so long i don't use it myself -- especially for "regular joins".

for outer joins, i'll definitely consider it, the syntax is easier.

for full outer joins -- well, i hope never to have to use them (so expensive), but if I do, i'll use the full outer join as that syntax makes it really easy.

how´s the predicate applied

A reader, December 27, 2003 - 4:56 pm UTC

I did some tests, here are the results

select e.first_name, e.last_name, d.department_name, e.department_id
from employees e
right join departments d
on e.department_id = d.department_id
and d.department_id in (60, 90)

select e.first_name, e.last_name, d.department_name, e.department_id
from departments d
left join employees e
on e.department_id = d.department_id
and d.department_id in (60, 90)


hr@ora92>explain plan for
2 select e.first_name, e.last_name, d.department_name, e.department_id
3 from employees e
4 right join departments d
5 on e.department_id = d.department_id
6 and d.department_id in (60, 90);

Explained.

hr@ora92>@plan

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 1485 | 56 |
| 1 | NESTED LOOPS OUTER | | 27 | 1485 | 56 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 |
| 3 | VIEW | | 1 | 39 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 18 | 2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
("D"."DEPARTMENT_ID"=60 OR "D"."DEPARTMENT_ID"=90) AND
("E"."DEPARTMENT_ID"=60 OR "E"."DEPARTMENT_ID"=90))

Note: cpu costing is off

19 rows selected.

hr@ora92>explain plan for
2 select e.first_name, e.last_name, d.department_name, e.department_id
3 from departments d
4 left join employees e
5 on e.department_id = d.department_id
6 and d.department_id in (60, 90);

Explained.

hr@ora92>@plan

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 1485 | 56 |
| 1 | NESTED LOOPS OUTER | | 27 | 1485 | 56 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 |
| 3 | VIEW | | 1 | 39 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 18 | 2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
("D"."DEPARTMENT_ID"=60 OR "D"."DEPARTMENT_ID"=90) AND
("E"."DEPARTMENT_ID"=60 OR "E"."DEPARTMENT_ID"=90))

Note: cpu costing is off

19 rows selected.

it seems that the AND always applies to EMPLOYEES, how so?

Tom Kyte
December 27, 2003 - 5:36 pm UTC

I'm not sure what you point is here??

the left and right joins both work the same -- the results are the same.

what are you trying to show?

the point is the AND after ON

A reader, December 27, 2003 - 7:09 pm UTC

Hi

what I would like to know why the AND d.department_id in (60, 90) is always applied to EMPLOYEES table no matter the order of join, doesnt it suppose to apply to the outer table? For example

from employees e
right join departments d

applies to employees e

from departments d
left join employees e

applies to department d

but from the execution plan it always applies to employees e

Tom Kyte
December 27, 2003 - 7:12 pm UTC

it is applied to the table being outer joined to.

e right join d

is the same as

d left join e

they are identical.

more qns on

RB, June 10, 2004 - 10:41 am UTC

I have two tables:
TABLE1(id1, id2, name1, name2)
TABLE2(id1, id2, samplevalue, sampletime)

I would like to get a result set with id1, id2 and name1 from TABLE1 and samplevalue and sampletime from TABLE2. id1 and id2 are primary keys in TABLE1 and id1 and id2 are fk in TABLE2. When I join these two tables I am getting a cartesian product.

Any help is greatly appreciated.

RB

Tom Kyte
June 10, 2004 - 5:10 pm UTC

hows about showing us your attempted query and then we'll comment. this is about as basic as a join gets?

where table1.id1 = table2.id2 and table1.id2 = table2.id2



Here is my query and more explanation

RB, June 14, 2004 - 1:35 pm UTC

Yes Indeed - this is a basic Join issue but I am not sure where I am making the mistake.

I would like to do a join between 2 tables (actually two views) where some of the values of 1 table dictate the filtering of the resultset and 1 other column is included in the resultset. Here are the 2 tables (actually views):

COUNTERS
------------------------------
VMID NUMBER 22, 38, 0 Not Null false
COUNTERID NUMBER 22, 38, 0 Not Null false
NAME VARCHAR2 255, 255, 0 Nullable false
INSTANCE VARCHAR2 255, 255, 0 Nullable false
DESCRIPTION VARCHAR2 255, 255, 0 Nullable false

DATA
--------------------------
VMID NUMBER 22, 38, 0 Not Null false
COUNTERID NUMBER 22, 38, 0 Not Null false
INTERVALID NUMBER 22, 38, 0 Not Null false
SAMPLEDEPTH NUMBER 0 Nullable false
SAMPLETIME DATE 0 Nullable false
SAMPLEVALUE NUMBER 22, 38, 0 Nullable false

I'd like to get rows from the DATA table that match a particular VMID, INTERVALID, and COUNTERID from the COUNTERS table. I have the query to do that (see Query1 below). What I'd like to be able to do, in addition, is to also get the corresponding INSTANCE column value for the COUNTERID from COUNTERS in in the resultset. In other words, I'd like the resultset to look like this:

VMID COUNTERID SAMPLETIME SAMPLEVALUE INSTANCE
---- --------- ------------------- ----------- ----------
1 9 2004-06-08 15:00:00 100 vmhba0:0:0


When I tried Query2, below, I couldn't get the appropriate INSTANCE value for the corresponding COUNTERID. It seemed to take a UNION of the 2 tables.

Query1
------
select
DATA.VMID,
DATA.COUNTERID,
DATA.SAMPLETIME,
DATA.SAMPLEVALUE
from
DATA
where
DATA.VMID=1
AND
DATA.INTERVALID=300
AND
DATA.COUNTERID IN (select
COUNTERS.COUNTERID
from
COUNTERS
where
COUNTERS.NAME='disk'
AND
COUNTERS.DESCRIPTION='kbpsRead'
AND
COUNTERS.VMID=1
)
;

Query2
------
select
DATA.VMID,
DATA.COUNTERID,
DATA.SAMPLETIME,
DATA.SAMPLEVALUE,
COUNTERS.INSTANCE
from
DATA,COUNTERS
where
DATA.VMID=1
AND
DATA.INTERVALID=300
AND
DATA.COUNTERID IN (select
COUNTERS.COUNTERID
from
COUNTERS
where
COUNTERS.NAME='disk'
AND
COUNTERS.DESCRIPTION='kbpsRead'
AND
COUNTERS.VMID=1
)
;



Tom Kyte
June 14, 2004 - 2:40 pm UTC

just

select whatever you want
from data, counters
where data.counterid = counters.counterid
and counters.name = 'disk'
and counters.description = 'kb....'
and counters.vmid = 1
and data.vmid = 1 and data.intervalid = 30;


just join.

Why

A reader, July 14, 2004 - 11:09 am UTC

Hi, Tom,

Why my output of explain is not the same as yours?
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN OUTER | | | | |
| 2 | SORT JOIN | | | | |
| 3 | TABLE ACCESS FULL | DEPT | | | |
| 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------

Note: rule based optimization, PLAN_TABLE' is old version

<========= NO FILTER/or ACCESS explain==========>
13 rows selected.

How should I do?

Thanks



Tom Kyte
July 14, 2004 - 12:07 pm UTC

see the message "plan table is old version"

drop your plan table
recreate it using @?/rdbms/admin/utlxplan

Outer Join bug in 10g (and in 9i too)

A reader, July 20, 2004 - 12:31 pm UTC

I ran the following in Oracle 9g. Apparently sth. wrong with the SQL Parser. Please remind (t3.t3.username should be invalid in both statements).

SELECT 1 FROM ALL_USERS t1
INNER JOIN ALL_USERS t2 ON (t1.username = t2.username)
INNER JOIN ALL_USERS t3 ON (t3.username = t2.username)
WHERE t3.t3.username = user
ORA-00904: "T3"."T3"."USERNAME": invalid identifier

SELECT 1 FROM ALL_USERS t1
INNER JOIN ALL_USERS t2 ON (t1.username = t2.username)
LEFT JOIN ALL_USERS t3 ON (t3.username = t2.username)
WHERE t3.t3.username = user
1
----------
1
1 row selected


Is this a known bug? Thanks.

Tom Kyte
July 20, 2004 - 8:32 pm UTC

I think the bug would be with the second query if any -- t3.t3??? there is no t3.t3.username.

why would it be t3.t3?

Hi

A reader, November 30, 2004 - 10:45 am UTC

You have mentioned in the beginning of this thread

"to join in 8i, just use a where clause:

select *
from emp, dept
where emp.deptno = dept.deptno;

in 9i and up, you could

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP inner join SCOTT.DEPT
on emp.deptno = dept.deptno"

Using the "inner join" is there any performance benefit compared to our normal join which we used to do in 8i?

If the performance is same I prefer using the 8i syntax (may be I am used to that).
Is there any other benefit using the "inner join"?


Thanks,


Tom Kyte
November 30, 2004 - 11:49 am UTC

nope, it is the same (or should be...)

Regarding Outer Join bug

John Spencer, November 30, 2004 - 1:42 pm UTC

I was intrigued by the Outer join issue raised by A Reader and I did a little playing.

Database version 9.2.0.5
O/S Version Solaris 9.5

CREATE TABLE t AS
SELECT rownum id
FROM all_objects
WHERE rownum <=10;
Because the explain plan from all_users was too hairy to read easily.

Query 1 is:
SELECT id FROM t t1
INNER JOIN t t2 ON (t1.id = t2.id)
LEFT JOIN t t3 ON (t3.id = t2.id)
WHERE t3.t3.id = 1;

And yes, t3.t3.id is intended, and does return a row. However, WHERE t1.t1.id or WHERE t2.t2.id raises ORA-00904. The syntax above is also accepted in an anonomous PL/SQL block, or in a CREATE PROCEDURE statement, while duplicating any other alias again raises ORA-00904.

Query 2 is:
SELECT 1
FROM t t1, t t2, t t3
WHERE t1.id = t2.id and
t1.id = t3.id(+) and
t3.id = 1;
The t3.t3.id version of this correctly gives:
ORA-00904: "T3"."T3"."ID": invalid identifier

Query 1 using RBO
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=842 Card=8000 Bytes=312000)
1 0 MERGE JOIN (CARTESIAN) (Cost=842 Card=8000 Bytes=312000)
2 1 MERGE JOIN (CARTESIAN) (Cost=42 Card=400 Bytes=10400)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=20 Bytes=260)
4 2 BUFFER (SORT) (Cost=40 Card=20 Bytes=260)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=20 Bytes=260)
6 1 BUFFER (SORT) (Cost=840 Card=20 Bytes=260)
7 6 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=20 Bytes=260)

Query 2 using RBO
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 FILTER
2 1 MERGE JOIN (OUTER)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'T'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'T'
8 2 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'T'

Query 1 Using CBO
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=102 Card=281 Bytes=10959)
1 0 MERGE JOIN (CARTESIAN) (Cost=102 Card=281 Bytes=10959)
2 1 MERGE JOIN (CARTESIAN) (Cost=16 Card=43 Bytes=1118)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=7 Bytes=91)
4 2 BUFFER (SORT) (Cost=14 Card=7 Bytes=91)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=7 Bytes=91)
6 1 BUFFER (SORT) (Cost=100 Card=7 Bytes=91)
7 6 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=7 Bytes=91)

After analyzing the table, both queries came up with the same plan (Query 1's version with accurate statistics.
I have a couple of questions about this.

1. Why am I getting statistics from the RBO plan using the ANSI Syntax? Does the ANSI syntax require CBO?

2. Why is there such a huge difference between the estimated statistics of the RBO plan compared to the CBO plan without statistics?

3. And, I guess the big question, why on earth does this work?

Any insights appreciated.

Thanks
John

Tom Kyte
November 30, 2004 - 2:03 pm UTC

1) some of it does, yes. many many many features require the CBO.

2) RBO used different heuristics to feed into the CBO it would appear. I didn't see any dynamic sampling going on - so they must just feed different defaults.

3) not sure why or if that syntax is technically correct (don't have the sql99 wire diagrams sitting here).

looks strange indeed ...

Gabe, November 30, 2004 - 2:36 pm UTC

flip@FLOP> SELECT t1.id FROM t t1
2 INNER JOIN t t2 ON (t1.id = t2.id)
3 LEFT JOIN t t3 ON (t3.id = t2.id)
4 WHERE whatever_you_fancy.t3.id = 1;

ID
----------
1


Tom Kyte
November 30, 2004 - 8:17 pm UTC

OK, that would be a bug... I'm way over convinced. I'll file one.

Cannot wait to see what correlation name I'll use in the test case ;)

OK

Siva, December 19, 2004 - 12:21 am UTC

Hi Tom,
In Oracle 10G,we have Grouped table Outer join.What is it
and how it works?
Please reply.
Bye!


Tom Kyte
December 19, 2004 - 11:00 am UTC

</code> https://asktom.oracle.com/Misc/oramag/on-uniqueness-space-and-numbers.html <code>

see partitioned outer join. "grouped outer join" was a "never actually released" feature, it was re-syntaxed and renamed before 10g went out. It is more generally refered to as "a partitioned outer join"

OK

Gerhard, December 23, 2004 - 1:28 pm UTC

Hi Tom,
Recently in Oracle Apps. I have seen a query used to build a view as follows

SQL > create view ar_cust_trx_lines_v
as select .... from ar_lookups arl,hz_parties party
where arl.lookup_code (+) = 'CATEGORY_CODE' and
arl.lookup_code(+) = party.category_code;

My doubt is what is the purpose of this line

" where arl.lookup_code (+) = 'CATEGORY_CODE' "

What we are testing here?

Do you have any simple query to explain this?
Please do reply.
Bye!


Tom Kyte
December 23, 2004 - 1:47 pm UTC

It is saying "show me all of the rows in party AND if the category_code is 'CATEGORY_CODE', show me the value frmo the lookup table"

Consider:

ops$tkyte@ORA9IR2> create table t1 ( x int, category_code varchar2(15) );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( lookup_code varchar2(15), other_data int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 'abc' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 2, 'CATEGORY_CODE' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 3, 'def' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 'foo', 55 );
 
1 row created.
 
ops$tkyte@ORA9IR2> select *
  2    from t1, t2
  3   where t1.category_code = t2.lookup_code(+)
  4     and 'CATEGORY_CODE' = t2.lookup_code(+)
  5  /
 
         X CATEGORY_CODE   LOOKUP_CODE     OTHER_DATA
---------- --------------- --------------- ----------
         2 CATEGORY_CODE
         1 abc
         3 def
 
<b>we always get every row, and if a row with CATEGORY_CODE was to appear:</b>


ops$tkyte@ORA9IR2> insert into t2 values ( 'CATEGORY_CODE', 42 );
 
1 row created.
 
ops$tkyte@ORA9IR2> select *
  2    from t1, t2
  3   where t1.category_code = t2.lookup_code(+)
  4     and 'CATEGORY_CODE' = t2.lookup_code(+)
  5  /
 
         X CATEGORY_CODE   LOOKUP_CODE     OTHER_DATA
---------- --------------- --------------- ----------
         2 CATEGORY_CODE   CATEGORY_CODE           42
         1 abc
         3 def
 
ops$tkyte@ORA9IR2>


<b>we get that as well, if the outer join to the literal is confusing, think of it like this:</b>


ops$tkyte@ORA9IR2> select *
  2    from (select t1.*, 'CATEGORY_CODE' extra from t1) t1, t2
  3   where t1.category_code = t2.lookup_code(+)
  4     and t1.extra = t2.lookup_code(+)
  5  /
 
         X CATEGORY_CODE   EXTRA         LOOKUP_CODE     OTHER_DATA
---------- --------------- ------------- --------------- ----------
         2 CATEGORY_CODE   CATEGORY_CODE CATEGORY_CODE           42
         1 abc             CATEGORY_CODE
         3 def             CATEGORY_CODE
 
ops$tkyte@ORA9IR2>


as if party (t1) had another column...


 

ANSI syntax

mikito, December 23, 2004 - 2:33 pm UTC

The reason outer join ANSI syntax makes sense is that outer join is more complex operation. Outer joins are not associative among themselves

(A outer join B) outer join C
is not the same as
A outer join (B outer join C)

Outer joins don't even associate with normal join

(A outer join B) join C
is not the same as
A outer join (B join C)

Hence we need parenthesis.

Compare this to ordinary join. It is associative, commutative and even commutes with selection. You just build cross product of A, B, and C and apply predicates in any reasonable order (the sooner predicates are applied the better performance wise). This is why the comma delimited syntax in the from clause is so intuitive.

Unfortunately, after inventing outer join keywords ANSI folks didn't stop and went on introducing INNNER and NATURAL joins. Those are just useless. Try writing

select * from A,B,C
where a.x+b.y=c.z and b.x+c.y=a.z and c.x+a.y=b.z

in ANSII syntax, if you are still not convinced.


Tom Kyte
December 23, 2004 - 3:06 pm UTC

and if you had a query with that join condition I would question "why" seriously.


the ansi syntax is actually very nice for people who have to look at other peoples sql


select *
from a join b on (a.x = b.y)
where a.c = 5
and b.d = 44
and a.sales > b.qty;


I can "understand" that better than just a big where clause -- instantly we see predicates (selection) vs joins.

Make it 3 table join

mikito, December 23, 2004 - 3:48 pm UTC

Example of complex query with 2 table join? Please, add one more table to be more realistic.

Besides, where "a.sales > b.qty" join predicate belongs? To the "where" clause, or to the "on" clause?

Tom Kyte
December 23, 2004 - 4:28 pm UTC

a.sales > b.qty is a predicate -- should be trivially clear that it is by the *very fact* it is not in the ON, it isn't a JOIN, it is a FILTER. So why "besides", that is the nice part of the ansi join syntax there. You don't have to ask that question "is that a join or a predicate", it is obvious from the syntax.


select *
from t1 join t2 on (t1.a = t2.d) join t3 on (t2.e = t3.g)
/


just keep joining away? i don't see your point I guess.


What is happening here??

Justin, January 25, 2005 - 5:37 pm UTC

I have two tables:

DROP TABLE t093;
CREATE TABLE t093 (t093_id NUMBER(10) NOT NULL);

INSERT INTO t093 VALUES(1);

DROP TABLE t094;

CREATE TABLE t094
(t094_id NUMBER(10,0) NOT NULL,
t093_id NUMBER(10,0) NOT NULL,
address_type_cd VARCHAR2(4) NOT NULL,
effective_dt DATE);

The first table represents a business, and the second table represents an address of that business, which may or may not exist.
As you can see, we now have one business, with no address information at all.

I am at a loss as to why QUERY1 returns no rows, VS. QUERY2 returns a row, and then looking at QUERY3 for another interesting perspective of the problem.

I have a feeling this is a disconnect in my logic.

*************************************************
QUERY1
*************************************************
justin@DEV> SELECT t1.t093_id
2 FROM t093 t1 LEFT JOIN t094 t2 ON t2.t093_id = t1.t093_id AND t2.address_type_cd IN ('M', 'P')
3 WHERE ( ( ( t2.effective_dt = (SELECT MAX(it1.effective_dt)
4 FROM t094 it1
5 WHERE t2.t093_id = it1.t093_id
6 AND it1.address_type_cd IN ('M', 'P')
7 )
8 AND t2.effective_dt <= sysdate
9 )
10 OR t2.effective_dt IS NULL
11 )
12 AND ( t2.t094_id = (SELECT MAX(it2.t094_id)
13 FROM t094 it2
14 WHERE t2.t093_id = it2.t093_id
15 AND it2.address_type_cd IN ('M', 'P')
16 )
17 OR t2.t094_id IS NULL
18 )
19 )
20 AND t1.t093_id = 1;

no rows selected

*************************************************
QUERY2
*************************************************
justin@DEV> SELECT t1.t093_id
2 FROM t093 t1 LEFT JOIN t094 t2 ON t2.t093_id = t1.t093_id --AND t2.address_type_cd IN ('M', 'P')
3 WHERE ( ( ( t2.effective_dt = (SELECT MAX(it1.effective_dt)
4 FROM t094 it1
5 WHERE t2.t093_id = it1.t093_id
6 AND it1.address_type_cd IN ('M', 'P')
7 )
8 AND t2.effective_dt <= sysdate
9 )
10 OR t2.effective_dt IS NULL
11 )
12 AND ( t2.t094_id = (SELECT MAX(it2.t094_id)
13 FROM t094 it2
14 WHERE t2.t093_id = it2.t093_id
15 AND it2.address_type_cd IN ('M', 'P')
16 )
17 OR t2.t094_id IS NULL
18 )
19 )
20 AND t1.t093_id = 1;

T093_ID
==========
1

1 row selected.

*************************************************
QUERY3
*************************************************
SELECT t1.t093_id
FROM t093 t1 LEFT JOIN t094 t2 ON t2.t093_id = t1.t093_id AND t2.address_type_cd IN ('M', 'P')
WHERE ( ( ( t2.effective_dt = (SELECT MAX(it1.effective_dt)
FROM t094 it1
WHERE t2.t093_id = it1.t093_id
AND it1.address_type_cd IN ('M', 'P')
)
AND t2.effective_dt <= sysdate
)
OR t2.effective_dt IS NULL
)
AND ( t2.t094_id = (SELECT MAX(it2.t094_id)
FROM t094 it2
WHERE t2.t093_id = it2.t093_id
AND it2.address_type_cd IN ('M', 'P')
)
OR t2.t094_id IS NOT NULL
)
)
AND t1.t093_id = 1;



Please help,
and thanks much!

Tom Kyte
January 25, 2005 - 7:36 pm UTC

9204 -- i see what you see
9205 (and up) -- i see what you should have seen.

looks like a corrected "ansi sql join bug"

another view of the problem??

Justin, January 25, 2005 - 5:56 pm UTC

QUERY4

SELECT t1.t093_id
FROM t093 t1 LEFT JOIN t094 t2 ON t2.t093_id = t1.t093_id
WHERE ( t2.address_type_cd IN ('M', 'P') OR t2.address_type_cd IS NULL)
AND ( t2.t094_id = (SELECT MAX(it1.t094_id)
FROM t094 it1
WHERE it1.t093_id = t1.t093_id
AND it1.address_type_cd IN ('M', 'P')
AND it1.effective_dt = (SELECT MAX(it2.effective_dt)
FROM t094 it2
WHERE it2.t093_id = it1.t093_id
AND it2.address_type_cd IN ('M', 'P')
)
)
OR t2.t094_id IS NULL
)
AND t1.t093_id = 1;



Thank you very much for the input

Justin, January 26, 2005 - 8:24 am UTC

What if I can't go to 9.2.0.5?

Can I write this query correctly *with fairly minor changes* even with the existence of this bug?

In otherwords, is there a way to make this query correct without completely overhauling to analytic functions etc?

Could you show me?

Thank you so much,

Justin

Tom Kyte
January 26, 2005 - 9:20 am UTC

SELECT t1.t093_id
FROM t093 t1,
(select * from t094 t2 where address_type_cd IN ('M', 'P') ) t2
WHERE t2.t093_id(+) = t1.t093_id
and
( ( ( t2.effective_dt = (SELECT MAX(it1.effective_dt)
FROM t094 it1
WHERE t2.t093_id = it1.t093_id
AND it1.address_type_cd IN ('M', 'P')
)
AND t2.effective_dt <= sysdate
)
OR t2.effective_dt IS NULL
)
AND ( t2.t094_id = (SELECT MAX(it2.t094_id)
FROM t094 it2
WHERE t2.t093_id = it2.t093_id
AND it2.address_type_cd IN ('M', 'P')
)
OR t2.t094_id IS NULL
)
)
AND t1.t093_id = 1;


(although going analytic is certainly a valid approach here too -- when you see yourself keeping the "max" record, that screams ANALYTICS)

how about this?

Justin, January 26, 2005 - 8:32 am UTC

SELECT t1.t093_id
FROM t093 t1 LEFT JOIN t094 t2 ON t2.t093_id = t1.t093_id
WHERE (t2.address_type_cd IN ('M', 'P') OR t2.address_type_cd IS NULL)
AND ( t2.t094_id = (SELECT MAX(it1.t094_id)
FROM t094 it1
WHERE it1.t093_id = t1.t093_id
AND it1.address_type_cd IN ('M', 'P')
AND it1.effective_dt = (SELECT MAX(it2.effective_dt)
FROM t094 it2
WHERE it2.t093_id = it1.t093_id
AND it2.address_type_cd IN ('M', 'P')
)
)
OR t2.t094_id IS NULL
)
AND t1.t093_id = 1;



How about this..

Agung, March 23, 2005 - 10:47 am UTC

Tom,

How do you convert the following ansi join into old-fashion "where" clause:

select ...
from sample s
left join assay a on s.sequence = a.sequence
left join loi l on s.sequence = l.sequence
left join h2o h on s.sequence = h.sequence
inner join collar c on s.hole_no = c.hole_no
inner join hole_type T on C.hole_type_code = T.hole_type_code
left join fraction F on c.hole_type_code = F.hole_type_code and S.fraction_code = F.Fraction_Code

I'm not familiar yet with ansi join so I need the "where" clause version to better understand the query.

Thanks

Tom Kyte
March 23, 2005 - 6:07 pm UTC

left joins are outer joins a = b(+)

inner joins are "normal" =



ANSI join: too many keywords, little point

Mikito Harakiri, March 24, 2005 - 12:55 pm UTC

<Tom's quote>I can "understand" that better than just a big where clause -- instantly we see predicates (selection) vs joins. </quote>

Like in this query?

select 1
from dual d1 join dual on d1.dummy='';


Tom Kyte
March 24, 2005 - 4:01 pm UTC

no, like in this query (imagine a 2 page query here with 10 tables, 20 predicates in addition to the joins)


lots of point (for me - I can see "ah hah, A left join B on (condition)" instead of digging deep for "ok, what are we joining to what"




I'm not using USING

Duke Ganote, June 08, 2005 - 3:03 pm UTC

An annoying part of the ANSI joins is the USING clause. It appears convenient:
1 select count(*)
2 from operator FULL OUTER join operator_role
3* USING (operator_id)
14:48:56 CIDW\cidwview> /

COUNT(*)
--------------------
5,216,645

but gets annoying quickly:

14:50:51 CIDW\cidwview> ed
Wrote file afiedt.buf

1 select count(*), case when O.operator_id is null then 'missing operator'
2 when R.operator_id is null then 'missing role'
3 else 'OK to join'
4 end
5 from operator o FULL OUTER join operator_role R
6 using (operator_id)
7 group by case when O.operator_id is null then 'missing operator'
8 when R.operator_id is null then 'missing role'
9 else 'OK to join'
10* end
14:51:12 CIDW\cidwview> /
when R.operator_id is null then 'missing role'
*
ERROR at line 8:
ORA-00904: "R"."OPERATOR_ID": invalid identifier


Basically, the ON clause appears to be mandatory when the join columns are used anywhere else in the SQL:

1 select count(*), case when O.operator_id is null then 'missing operator'
2 when R.operator_id is null then 'missing role'
3 else 'OK to join'
4 end
5 from operator o FULL OUTER join operator_role R
6 ON (o.operator_id = r.operator_id)
7 group by case when O.operator_id is null then 'missing operator'
8 when R.operator_id is null then 'missing role'
9 else 'OK to join'
10* end
14:51:40 CIDW\cidwview> /

COUNT(*) CASEWHENO.OPERAT
-------------------- ----------------
13,230 missing operator
5,086,890 OK to join
116,525 missing role

Otherwise you can end up with ORA-25154 and similar error messages (I'm using 9i).

Self Join ANSI

Duke Ganote, June 14, 2005 - 7:06 pm UTC

I'm attempting to find, just by column_name, the different columns in two tables using the ANSI full outer join syntax.  Here's the set up:

SQL> create table X ( A number, B number );
Table created.
SQL> create table Y ( B number, C number );
Table created.

Here's the query in 9.2.0.1.0:

  1   select x.column_name, y.column_name
  2     from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
  3       on X.column_name = Y.column_name
  4    where X.table_name = 'X'
  5      and Y.table_name = 'Y'
  6*     and ( X.column_name IS NULL or Y.column_name IS NULL )
SQL> /
 select x.column_name, y.column_name
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE

Well, that was an older version of Oracle.  I tried it on 10.1.0.2.0 with the following result:


  1  select x.column_name, y.column_name
  2    from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
  3      on X.column_name = Y.column_name
  4   where X.table_name = 'X'
  5     and Y.table_name = 'Y'
  6*    and ( X.column_name IS NULL or Y.column_name IS NULL )
SQL> /
  from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
                                          *
ERROR at line 2:
ORA-00918: column ambiguously defined

I am missing something? 

Tom Kyte
June 15, 2005 - 3:17 am UTC

neither of those queries work with your supplied tables at all. neither has a column named "column_name" or "table_name"

Predicated upon...

Duke Ganote, June 15, 2005 - 8:46 am UTC

Ah, nothing like a night to sleep on it: here's the result on a corrected query on the USER_TAB_COLUMNS in 10.1.0.2.0:

10.1.0.2.0> ed
Wrote file afiedt.buf

1 SELECT *
2 FROM
3 ( select x_col, y_col
4 from ( select column_name AS X_COL
5 from user_tab_columns
6 WHERE table_name = 'X' ) XCOLS
7 FULL OUTER JOIN
8 ( select column_name AS Y_COL
9 from user_tab_columns
10 where table_name = 'Y' ) YCOLS
11 ON X_COL = Y_COL )
12* WHERE ( x_col IS NULL or y_col IS NULL )
10.1.0.2.0> /

X_COL Y_COL
------------------------------ ------------------------------
A

HOWEVER I get my expected result when I run it on a "real(ized)" table in 10g, that is, when I create
10.1.0.2.0> create table utc as
select table_name, column_name from user_tab_columns;
and run the query on that table, -OR- when I run the same query in 9i:

9.2.0.1.0> ed
Wrote file afiedt.buf

1 SELECT *
2 FROM
3 ( select x_col, y_col
4 from ( select column_name AS X_COL
5 from user_tab_columns
6 WHERE table_name = 'X' ) XCOLS
7 FULL OUTER JOIN
8 ( select column_name AS Y_COL
9 from user_tab_columns
10 where table_name = 'Y' ) YCOLS
11 ON X_COL = Y_COL )
12* WHERE ( x_col IS NULL or y_col IS NULL )
9.2.0.1.0> /

X_COL Y_COL
------------------------------ ------------------------
A
C

Tom Kyte
June 15, 2005 - 10:09 am UTC

that would be a bug, i reproduced in 9i as well as 10g. It will be an optimizer related bug.

Please contact support with your test case.

TAR 4440770.993

Duke Ganote, June 16, 2005 - 2:27 pm UTC

per your advice, I created TAR 4440770.993

Tom Kyte
June 16, 2005 - 2:55 pm UTC

great, if it gets "bogged down" for any reason - let me know.

TAR 4440770.993 -> Bug 4456374

Duke Ganote, June 27, 2005 - 7:27 am UTC

26-JUN-05 01:09:32 GMT
STATUS
=======
Bug 4456374 has been filed

26-JUN-05 03:26:59 GMT
Associated bug 4456374 has been updated and has changed status to 10.

Workaround

Duke Ganote, June 29, 2005 - 4:55 pm UTC

Here's the reply.  My test of the workaround follows.
**********************************************************
29-JUN-05 20:22:39 GMT UPDATE
======== 
DEV had indicated that the bug filed on your behalf is actually a duplicate of another bug. This second bug is unpublished but it will be fixed in 10.1.0.5 and 10.2.           

Workaround:  Use the WITH clause for the subquery containing the ANSI join
**********************************************************
And that workaround appears to work:

BEFORE SQL> ed
Wrote file afiedt.buf

  1   SELECT *
  2     FROM
  3          ( select x_col, y_col
  4             from ( select column_name AS X_COL
  5                          from user_tab_columns
  6                      WHERE table_name = 'X' ) XCOLS
  7          FULL OUTER JOIN
  8         ( select column_name AS Y_COL
  9             from user_tab_columns
 10            where table_name = 'Y' ) YCOLS
 11        ON X_COL = Y_COL   )
 12* WHERE ( x_col IS NULL or y_col IS NULL )
BEFORE SQL> /

X_COL                          Y_COL
------------------------------ ------------------------------
A

AFTER SQL> ed
Wrote file afiedt.buf

  1  with workaround_query AS
  2  (
  3   SELECT *
  4     FROM
  5          ( select x_col, y_col
  6             from ( select column_name AS X_COL
  7                          from user_tab_columns
  8                      WHERE table_name = 'X' ) XCOLS
  9          FULL OUTER JOIN
 10         ( select column_name AS Y_COL
 11             from user_tab_columns
 12            where table_name = 'Y' ) YCOLS
 13        ON X_COL = Y_COL   )
 14  WHERE ( x_col IS NULL or y_col IS NULL )
 15  )
 16* select * from workaround_query
AFTER SQL> /

X_COL                          Y_COL
------------------------------ ------------------------------
A
                               C 

Tom Kyte
June 29, 2005 - 8:20 pm UTC

thanks for the followup!

One more trick on the workaround

Duke Ganote, June 30, 2005 - 5:20 pm UTC

As noted previously, this query fails with ORA-00918:
--------------
  1  select x.column_name, y.column_name
  2    from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
  3      on X.column_name = Y.column_name
  4   where X.table_name = 'X'
  5     and Y.table_name = 'Y'
  6*    and ( X.column_name IS NULL or Y.column_name IS NULL )

The "obvious" (to me) application of the workaround didn't (work):
--------------
  1  with failing_workaround as
  2  (
  3   select x.column_name, y.column_name
  4     from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
  5       on X.column_name = Y.column_name
  6    where X.table_name = 'X'
  7      and Y.table_name = 'Y'
  8      and ( X.column_name IS NULL or Y.column_name IS NULL )
  9  )
 10* select * from failing_workaround
SQL> /
   from user_tab_columns X FULL OUTER JOIN user_tab_columns Y
                                           *
ERROR at line 4:
ORA-00918: column ambiguously defined

Support pointed out it needs a twist: 2 subqueries.
--------------
  1  with X as
  2  ( select column_name
  3      from user_tab_columns
  4     where table_name = 'X'
  5  ),
  6       Y as
  7  ( select column_name
  8      from user_tab_columns
  9     where table_name = 'Y'
 10  )
 11   select x.column_name, y.column_name
 12     from X FULL OUTER JOIN Y
 13       on X.column_name = Y.column_name
 14*   where X.column_name IS NULL or Y.column_name IS NULL
SQL> /

COLUMN_NAME                    COLUMN_NAME
------------------------------ ------------------------------
A
                               C 

Cartesian Product

A reader, July 08, 2005 - 12:37 pm UTC

Hi Tom,
SQL Books mention that we get Cartesian product (Cross product in 9i) if we dont have proper join conditions.
I would like to know under what conditions cartesian product will be really useful.

Thanks in advance.

Tom Kyte
July 08, 2005 - 1:03 pm UTC

scott-ORA10GR2> select empno, decode(r,1,'SAL','COMM') tag, decode(r,1,SAL,COMM) val
2 from emp, (select 1 r from dual union all select 2 r from dual )
3 order by 1,2;

EMPNO TAG VAL
---------- ---- ----------
7369 COMM
7369 SAL 800
7499 COMM 300
7499 SAL 1600
7521 COMM 500
7521 SAL 1250
7566 COMM
7566 SAL 2975
7654 COMM 1400
7654 SAL 1250
7698 COMM
7698 SAL 2850
7782 COMM
7782 SAL 2450
7788 COMM
7788 SAL 3000
7839 COMM
7839 SAL 5000
7844 COMM 0
7844 SAL 1500
7876 COMM
7876 SAL 1100
7900 COMM
7900 SAL 950
7902 COMM
7902 SAL 3000
7934 COMM
7934 SAL 1300

28 rows selected.


doing a pivot for example....

doing "nearest neighbor" searches

scott-ORA10GR2> l
1 select *
2 from (
3 select empno, diff, min(diff) over (partition by empno) min_diff, sal1, sal2, other_empno
4 from (
5 select a.empno, abs(a.sal-b.sal) diff, a.sal sal1, b.sal sal2, b.empno other_empno
6 from emp a, emp b
7 where a.empno <> b.empno
8 )
9 )
10* where diff = min_diff
scott-ORA10GR2> /

EMPNO DIFF MIN_DIFF SAL1 SAL2 OTHER_EMPNO
---------- ---------- ---------- ---------- ---------- -----------
7369 150 150 800 950 7900

7499 100 100 1600 1500 7844

7521 0 0 1250 1250 7654

7566 25 25 2975 3000 7902
25 25 2975 3000 7788

7654 0 0 1250 1250 7521

7698 125 125 2850 2975 7566

7782 400 400 2450 2850 7698

7788 0 0 3000 3000 7902

7839 2000 2000 5000 3000 7788
2000 2000 5000 3000 7902

7844 100 100 1500 1600 7499

7876 150 150 1100 1250 7521
150 150 1100 1250 7654
150 150 1100 950 7900

7900 150 150 950 800 7369
150 150 950 1100 7876

7902 0 0 3000 3000 7788

7934 50 50 1300 1250 7654
50 50 1300 1250 7521


20 rows selected.



there use is limited in 'real life', but there are real world uses for them.

Thanks

A reader, July 09, 2005 - 12:18 pm UTC

Thanks tom for your reply. i had been wondering where the cartesian product is useful when most of the books says its not useful in real life. Thanks again.


reader

A reader, October 13, 2005 - 4:16 pm UTC

Hi Tom,


i joined like this:

select last_name,d.department_name,job_id,e.department_id
from employees e,departments d,locations l
where e.department_id=d.department_id
and
d.location_id=l.location_id
and
city = 'Toronto';


but in answere it was like this:

select e.last_name,e.job_id,e.department_id,d.depart_name
from employees e join departments d
on(e.department_id=d.department_id)
join locations l
on(d.location_id=l.location_id)
where city = 'Toronto';

result of both was same....1)may i know what is the difference between my and the answere query and
2)which is apprpriate to write and
3) in what case do we use join condition in from clause and in what case in where clause

regards
sachin

Tom Kyte
October 13, 2005 - 8:55 pm UTC

1) one used "old style joins", and one use "new fangled ANSI join" syntax....

2) #1 is what you'll likely see most often - #2 is 'newish'. I personally am lukewarm on the new syntax. sometimes it makes the query easier to read - not always...

3) you can use either or, they are interchangeable.

ORA-01799

Karmit, October 15, 2005 - 6:37 pm UTC

Hi Tom,

I am having a problem with outer-join in this SQL.

This SQL is formed "dynamically" using bind variables
and due to ORA-01799 I'm not able to get the desired
result. Any solution would be welcome!

Test Case:
=========

create table ett(
ett_key number primary key
,ett_code varchar2(10)
);
insert into ett(ett_key, ett_code) values (1,'a');
insert into ett(ett_key, ett_code) values (2,'b');
insert into ett(ett_key, ett_code) values (3,'c');
insert into ett(ett_key, ett_code) values (4,'d');
insert into ett(ett_key, ett_code) values (5,'e');
insert into ett(ett_key, ett_code) values (6,'f');
insert into ett(ett_key, ett_code) values (7,'g');
insert into ett(ett_key, ett_code) values (8,'h');
commit;


create table ett_prop_typ(
ept_key number primary key
,ept_code varchar2(10)
);
insert into ett_prop_typ(ept_key, ept_code) values(1001, 'prop1');
insert into ett_prop_typ(ept_key, ept_code) values(1002, 'prop2');
insert into ett_prop_typ(ept_key, ept_code) values(1003, 'prop3');
commit;


create table ett_prop(
etp_key number primary key
,etp_ett_key number
,etp_ept_key number
,etp_code varchar2(10)
);
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20001, 1, 1001, 'a');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20002, 1, 1002, 'a');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20003, 1, 1003, 'a');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20004, 2, 1001, 'b');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20005, 2, 1002, 'b');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20006, 2, 1003, 'b');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20007, 3, 1001, 'c');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20008, 3, 1002, 'c');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20009, 3, 1003, 'c');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20010, 4, 1001, 'd');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20011, 4, 1002, 'd');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20012, 5, 1001, 'e');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20013, 5, 1002, 'e');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20014, 5, 1003, 'e');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20015, 6, 1001, 'f');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20016, 6, 1002, 'f');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20017, 6, 1003, 'f');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20018, 7, 1001, 'g');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20019, 7, 1002, 'g');

insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20020, 8, 1001, 'h');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20021, 8, 1002, 'h');
insert into ett_prop(etp_key, etp_ett_key, etp_ept_key, etp_code) values (20022, 8, 1003, 'h');


commit;


Query 1 - Works, but not the desired result
===========================================
select t1.ett_key, t2.etp_code
from ett t1, ett_prop t2
where t1.ett_key = t2.etp_ett_key
and t2.etp_ept_key = (select ept_key
from ett_prop_typ
where ept_key = 1003)
;

-- "1003" is actually passed as a bind variable.


ETT_KEY ETP_CODE
---------- ----------
1 a
2 b
3 c
5 e
6 f
8 h

6 rows selected.


However, since I wish to get the "madeup" records
as well from t2 - I try using the following:



Query 2 - How I "feel" it should work!
======================================
select t1.ett_key, t2.etp_code
from ett t1, ett_prop t2
where t1.ett_key = t2.etp_ett_key(+)
and t2.etp_ept_key(+) = (select ept_key
from ett_prop_typ
where ept_key = 1003)
;

ERROR at line 7:
ORA-01799: a column may not be outer-joined to a subquery


This is the problem, since I desire the results to
appear as follows, which I can achieve by removing
the sub-query with the actual value i.e "1003".
But this will not help in my application, since
its bind variable driven and I'll not know the
code before-hand, thus sub-query is the way to
get that information.

So.. I want the application to use something like:

select t1.ett_key, t2.etp_code
from ett t1, ett_prop t2
where t1.ett_key = t2.etp_ett_key(+)
and t2.etp_ept_key(+) = (select ept_key
from ett_prop_typ
where ept_key = :b1)
;



Query 3 - desired result - but SQL hardcoded!!
==============================================
select t1.ett_key, t2.etp_code
from ett t1, ett_prop t2
where t1.ett_key = t2.etp_ett_key(+)
and t2.etp_ept_key(+) = 1003 -- HARDCODED!
;

ETT_KEY ETP_CODE
---------- ----------
1 a
2 b
3 c
4
5 e
6 f
7
8 h

8 rows selected.


Any idea how I can get the above results
without compromising on the flexibility?


Thanks,
Karmit

Tom Kyte
October 16, 2005 - 8:03 am UTC

ops$tkyte@ORA9IR2> variable b1 number
ops$tkyte@ORA9IR2> exec :b1 := 1003

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select b.ett_key, a.etp_code
  2    from (
  3  select *
  4    from ett_prop
  5   where etp_ept_key = (select ept_key
  6                          from ett_prop_typ
  7                         where ept_key = :b1 )
  8         )a,
  9         ett b
 10   where b.ett_key = a.etp_ett_key(+)
 11  /

   ETT_KEY ETP_CODE
---------- ----------
         1 a
         2 b
         3 c
         4
         5 e
         6 f
         7
         8 h

8 rows selected.
 

Outer join problem

Karmit, October 16, 2005 - 11:35 am UTC

Thats the one! Excellent.

Thanks!
Karmit

OK

Jayasankar, October 26, 2005 - 1:04 am UTC

Hi Tom,
What is the criteria for joining tables??

Suppose if I have some 10 tables having primary keys and
foreign keys,how to join them???

I mean,should foreign keys be joined to primary keys or
foreign keys to foreign keys or primary keys to
primary keys??

Tom Kyte
October 26, 2005 - 11:45 am UTC

you typically join a table to another table by the first tables foreign key to the second tables primary or unique key.

Another question about outer join

Reena, November 07, 2005 - 2:40 pm UTC

Hi Tom,

Thank you for all the help through this website. I am working on a query with an outer join, that's not really behaving the way I thought it would. I've created three simple tables to mimic the more complex tables that I'm working with for a test case.

I have three tables. A student table (t_term), an employee table (t_emp) and a department table (t_dept). The student table contains information the term, the department and the student id number (pid). The employee table contains information about the department and the employee id number (pid). The codes used for departments in the student table and employee table are different and the crosswalk for the codes can be found from the department table. There can be departments that don't have employees but have students, and departments that have employees but no students. And there can also be departments that don't have employees or students and we're not interested in displaying those.

I want to write a query, that would count the employees and students in each department for the term 'FA05' and not display any information for the departments that don't have both no employees and no students.

To generate my test case and execute my query the following can be done:

create table t_emp
(hrs_dept VARCHAR2(4),
pid VARCHAR2(5)
)
/
create table t_term
(term VARCHAR2(4),
major_dept VARCHAR2(4),
pid VARCHAR2(5)
)
/
create table t_dept
(hrs_dept VARCHAR2(4),
isis_dept VARCHAR2(4)
)
/
insert into t_emp(hrs_dept, pid)
values ('1001', '0001')
/
insert into t_emp(hrs_dept, pid)
values ('1001', '0002')
/
insert into t_emp(hrs_dept, pid)
values ('1005', '0003')
/
insert into t_emp(hrs_dept, pid)
values ('1019', '0004')
/
insert into t_emp(hrs_dept, pid)
values ('1019', '0005')
/
insert into t_emp(hrs_dept, pid)
values ('1019', '0006')
/
insert into t_dept (hrs_dept, isis_Dept)
values ('1001', 'ACIU')
/
insert into t_dept (hrs_dept, isis_dept)
values ('1005', 'GSIU')
/
insert into t_dept (hrs_dept, isis_dept)
values('1019', 'LIBR')
/
insert into t_dept (hrs_dept, isis_dept)
values ('0002', 'XXIP')
/
insert into t_dept (hrs_dept, isis_dept)
values ('1006', 'CIRA')
/
insert into t_term (term, major_dept, pid)
values ('FA05', 'ACIU', '100')
/
insert into t_term (term, major_dept, pid)
values ('FA05', 'ACIU', '101')
/
insert into t_term (term, major_dept, pid)
values ('SP04', 'GSIU', '102')
/
insert into t_term (term, major_dept, pid)
values ('FA05', 'XXIP', '103')
/

select emp.hrs_dept, dept.isis_dept, count(distinct emp.pid) no_of_emp, count(distinct trm.pid) no_of_stud
from t_emp emp, t_term trm, t_dept dept
where emp.hrs_dept(+) = dept.hrs_dept
and dept.isis_dept = trm.major_dept(+)
and (trm.term = 'FA05' or trm.term is null)
group by emp.hrs_dept, dept.isis_dept
having decode(count(distinct emp.pid), 0, 1, 0) * decode(count(distinct trm.pid), 0, 1, 0) != 1
order by emp.hrs_dept

I get the following result:

HRS_DEPT ISIS_DEPT NO_OF_EMP NO_OF_STUD
----------- ------------ ------------ -------------
1001 ACIU 2 2
1019 LIBR 3 0
(null) XXIP 0 1

As you can see the department 1005/GSIU has employees and no students, but gets dropped out. Where as the department 1019/LIBR has employees and no students, but comes through. My delimma is why does the 1005/GSIU get dropped out? It had students in a previous term but not in 'FA05', whereas the department 1019/LIBR never had any students.

If I create a view:

create view t_term_vw as
select * from t_term where term = 'FA05'

and then execute the following query:

select emp.hrs_dept, dept.isis_dept, count(distinct emp.pid) no_of_emp, count(distinct trm.pid) no_of_stud
from t_emp emp, t_term_vw trm, t_dept dept
where emp.hrs_dept(+) = dept.hrs_dept
and dept.isis_dept = trm.major_dept(+)
--and (trm.term = 'FA05' or trm.term is null)
group by emp.hrs_dept, dept.isis_dept
having decode(count(distinct emp.pid), 0, 1, 0) * decode(count(distinct trm.pid), 0, 1, 0) != 1
order by emp.hrs_dept

I get:

HRS_DEPT ISIS_DEPT NO_OF_EMP NO_OF_STUD
----------- ------------ ------------ -------------
1001 ACIU 2 2
1005 GSIU 1 0
1019 LIBR 3 0
(null) XXIP 0 1

This is what I really want, but don't necessarily want to create a view for this to work and I really don't understand why this is happening?

I would really appreciate it if you can explain this! I thought I understood outer joins really well, until this!

Tom Kyte
November 08, 2005 - 9:30 pm UTC

I did not quite follow everything here - especially the dept mapping you alluded to - however, if the last query against the view works and you deem this "right", then you can always use an inline view:


select emp.hrs_dept, dept.isis_dept, count(distinct emp.pid) no_of_emp,
count(distinct trm.pid) no_of_stud
from t_emp emp,
(select * from t_term where term = 'FA05') trm,
t_dept dept
where emp.hrs_dept(+) = dept.hrs_dept
and dept.isis_dept = trm.major_dept(+)
--and (trm.term = 'FA05' or trm.term is null)
group by emp.hrs_dept, dept.isis_dept
having decode(count(distinct emp.pid), 0, 1, 0) * decode(count(distinct
trm.pid), 0, 1, 0) != 1
order by emp.hrs_dept





It's just bugging me that I don't understand why it's working this way

Reena, November 10, 2005 - 12:29 pm UTC

Tom,

Thanks for your reply, and you're right I can do it with the inline view, but this is just so different from how I 'thought' the query would behave.

Let me explain my query and the 'dept' crosswalk table. The student table uses the letter department code and the employee table uses the number department code. This is a result of some new development that happened here and the student system has not yet been upgraded to use the new codes. But the department table has a crosswalk (mapping) of the new number codes to the old letter codes, and therefore is needed for this query.

The outer join in my query seems to be working partially, in that I am getting the 1019/LIBR which has employees but not students. And I'm also getting the row for the XXIP department which has students but no employees.

However I am not getting any rows for 1005/GSIU, which has employees but no students for the term 'FA05'. Note that it did have students in the term 'SP04'. But since my query is pertaining to 'FA05', it's not 'making up' rows (as it should in an outer join) for the t_term table for 'FA05'.

I would really appreciate if you can explain this behavior. It's just so different from how I thought outer joins worked and I would really like to understand this.

Thank you!

More info

Reena, November 10, 2005 - 12:44 pm UTC

I thought I would post this for clarity:

select * from t_emp

HRS_DEPT PID
----------- ------
1001 0001
1001 0002
1005 0003
1019 0004
1019 0005
1019 0006

select * from t_term

TERM MAJOR_DEPT PID
------- ------------- ------
FA05 ACIU 100
FA05 ACIU 101
SP04 GSIU 102
FA05 XXIP 103

select * from t_dept

HRS_DEPT ISIS_DEPT
----------- ------------
1001 ACIU
1005 GSIU
1019 LIBR
0002 XXIP
1006 CIRA


Thank you!

old and new syntax

Puja, November 30, 2005 - 3:36 am UTC

HI Tom,

Can you tell me which of this would be more efficient:

select * from
emp, dept
where emp.deptno = dept.deptno(+);

or

select * from emp
left outer join
dept
on emp.deptno = dept.deptno;

Regards,

Puja

Tom Kyte
November 30, 2005 - 11:40 am UTC

one would expect them to be "the same"

Misconception

Puja, December 02, 2005 - 1:54 am UTC

HI,

Thanks for clarifying that.

Lot of people do have this misconception that using the ANSI syntax is better than the old syntax(although there is no concrete reasoning to support that), hence this question.

Regards,
Puja

Tom Kyte
December 02, 2005 - 10:51 am UTC

the thought is that if you specify

a) the joins

and then

b) the predicates

it makes the query itself more readable, more understandable. Although once you get more than a handful of tables - it (ansi style) seems to become less readable though

Old vs New join: pros and cons

Duke Ganote, December 02, 2005 - 3:30 pm UTC

Oracle documentation recommends the ANSI style joins
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054067
ANSI syntax allows the use of partitioning
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10736/analysis.htm#sthref1746
HOWEVER, original Oracle-style outer joins allow the (+) operator on a predicate so the outer join isn't wasted:

select t1.*, t2.any_other_column
  from t1, t2
 where t1.x = t2.x(+)
   and t2.any_other_column(+) <some condition>
/
68,000 rows

versus

select t1.*, t2.any_other_column
  from t1, t2
 where t1.x = t2.x(+)
   and t2.any_other_column <some condition>
/
53,000 rows
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2062277 <code>

Tom Kyte
December 03, 2005 - 10:14 am UTC

huh? not following what you mean by "wasted" here at all.

ansi joins would permit both of the above results as well.

"wasted"

Duke Ganote, December 05, 2005 - 5:56 pm UTC

Sorry, by "wasted" I mean as you meant in your response at 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4963137609733

SET UP:

create table t1 as
select level X, level+0.5 some_column
  from dual connect by level < 6
/
create table t2 as
select level X, level+1.5 any_other_column
  from dual connect by level < 3
/

QUERIES AND RESULTS:

  1  select t1.*, t2.any_other_column
  2    from t1, t2
  3   where t1.x = t2.x(+)
  4*    and t2.any_other_column is not null
SQL> /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5

SQL> ed
Wrote file afiedt.buf

  1  select t1.*, t2.any_other_column
  2    from t1, t2
  3   where t1.x = t2.x(+)
  4*    and t2.any_other_column(+) is not null
SQL> /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5
         5         5.5
         4         4.5
         3         3.5

SQL> ed
Wrote file afiedt.buf

  1  select t1.*, t2.any_other_column
  2    from t1 LEFT OUTER JOIN t2
  3         ON ( t1.x = t2.x )
  4*  where t2.any_other_column(+) is not null
SQL> /
 where t2.any_other_column(+) is not null
       *
ERROR at line 4:
ORA-25156: old style outer join (+) cannot be used with ANSI joins

The results can be achieved with ANSI joins, but like this:

  1  select t1.*, t2.any_other_column
  2    from t1 LEFT OUTER JOIN
  3         ( SELECT * FROM t2
  4            WHERE t2.any_other_column IS NOT NULL ) t2
  5*        ON ( t1.x = t2.x )
SQL> /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5
         5         5.5
         4         4.5
         3         3.5 

Tom Kyte
December 06, 2005 - 5:34 am UTC

still not following - you either use

a) old style
b) ansi style

but not both


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select t1.*, t2.any_other_column
  2    from t1 LEFT OUTER JOIN t2
  3    ON ( t1.x = t2.x and t2.any_other_column is not null )
  4  /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5
         5         5.5
         4         4.5
         3         3.5

works as well... 

Nice...

Duke Ganote, December 06, 2005 - 9:29 am UTC

Didn't realize you could put a predicate within the ON clause...

SQL> ed
Wrote file afiedt.buf

  1  select t1.*, t2.any_other_column
  2    from t1 LEFT OUTER JOIN t2
  3         ON ( t1.x = t2.x
  4*             and t2.any_other_column is not null )
SQL> /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5
         5         5.5
         4         4.5
         3         3.5

SQL> ed
Wrote file afiedt.buf

  1  SELECT t1.*, t2.any_other_column
  2    FROM t1 LEFT OUTER JOIN t2
  3         ON ( t1.x = t2.x )
  4*  WHERE t2.any_other_column is not null
SQL> /

         X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
         1         1.5              2.5
         2         2.5              3.5

I found this in the documentation:

"Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause." 
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#i2080416
but wouldn't have guessed that was an implication... 

A reader, December 29, 2005 - 12:07 pm UTC

Hi Tom

may i know please why this below query works without specifying which location_id(because location_id is in both the tables with the same exact name) and why it does not work like in 2nd query

SELECT location_id, city, department_name
FROM departments NATURAL JOIN locations;

2)SELECT l.location_id, city, department_name
FROM departments NATURAL JOIN locations l;

Tom Kyte
December 29, 2005 - 1:15 pm UTC

that is the nature of the "natural join" which should be avoided like the plague.

location_id, in the natural join, is just there, it doesn't "belong" to either table, it is assumed - since it was the natural join key.

See how there is just "1" X

ops$tkyte@ORA9IR2> create table t1 ( x int, a int );

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, b int );

Table created.

ops$tkyte@ORA9IR2> insert into t1 values ( 1, 1 );

1 row created.

ops$tkyte@ORA9IR2> insert into t2 values ( 1, 2 );

1 row created.

ops$tkyte@ORA9IR2> select * from t1 natural join t2;

         X          A          B
---------- ---------- ----------
         1          1          2

and now there are two:

ops$tkyte@ORA9IR2> select * from t1, t2 where t1.x = t2.x;

         X          A          X          B
---------- ---------- ---------- ----------
         1          1          1          2



when using the natural join - there is just "X", not t1.x, not t2.x, just "X"

ops$tkyte@ORA9IR2>  select t1.x from t1 natural join t2;
 select t1.x from t1 natural join t2
        *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier



but avoid natural joins - period, don't even consider using them for anything. 

CARTISIAN

Asim Naveed, January 02, 2006 - 2:28 am UTC

CREATE TABLE dual2
( c1 NUMBER(1));

INSERT INTO dual2 values(1);

Now if in any SQL query, I just add dual2 in the FROM
clause of that query, will it make any big difference with
respect to execution time of that query. Note that I am
just writing ", dual2 " at the end of FROM clause table list, and nothing in the WHERE clause.

For some reason, we have to do this in a too we are using
for generating queries.



Predicates in the ON clause

Duke Ganote, January 26, 2006 - 4:39 pm UTC

You can't impose a condition on the 'driving' table in the ON clause, just the outer-joined table?

1 select t1.*, t2.any_other_column
2 from t1 LEFT OUTER JOIN t2
3 ON ( t1.x = t2.x
4 and t2.any_other_column is not null
5* AND t1.X < 5 ) -- ignored
XPLT9173:GR2\dganote> /

X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
1 1.5 2.5
2 2.5 3.5
3 3.5
4 4.5
5 5.5

1 with t1X as ( select * from t1 WHERE X < 5 )
2 select t1.*, t2.any_other_column
3 from t1X t1 LEFT OUTER JOIN t2
4 ON ( t1.x = t2.x
5* and t2.any_other_column is not null )
XPLT9173:GR2\dganote> /

X SOME_COLUMN ANY_OTHER_COLUMN
---------- ----------- ----------------
1 1.5 2.5
2 2.5 3.5
4 4.5
3 3.5

Would you point me to some documentation explaining its usage?

Tom Kyte
January 27, 2006 - 8:22 am UTC

no create table, no inserts, no look.

Set up

Duke Ganote, January 27, 2006 - 10:43 am UTC

Sorry, I was getting spoiled by my first experiences in the Discussion Forum, where I can directly reference a previous posting 'up the chain'. The setup is from </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187#52788078259828 <code>

SET UP:

create table t1 as
select level X, level+0.5 some_column
from dual connect by level < 6
/
create table t2 as
select level X, level+1.5 any_other_column
from dual connect by level < 3
/

COMPARED QUERIES:

select t1.*, t2.any_other_column
from t1 LEFT OUTER JOIN t2
ON ( t1.x = t2.x
and t2.any_other_column is not null
AND t1.X < 5 )
/
with t1X as ( select * from t1 WHERE X < 5 )
select t1.*, t2.any_other_column
from t1X t1 LEFT OUTER JOIN t2
ON ( t1.x = t2.x
and t2.any_other_column is not null )
/


Tom Kyte
January 27, 2006 - 11:30 am UTC

I'll investigate..

Duke's example

mikito harakiri, January 27, 2006 - 2:49 pm UTC

Selection operator doesn't commute with outer join.

Tom Kyte
January 28, 2006 - 12:43 pm UTC

doesn't matter, if you get different answers from a table created as select from a view VS using the view itself - that is "not right"

Duke's Question

Dave C, January 27, 2006 - 4:40 pm UTC

Duke,

It's a function of how ANSI defines outer joins: all rows in the preserved table are preserved. You can't preserve a row and eliminate it at the same time, so it's preserved in the JOIN and then optionally discarded in the WHERE.

Think of outer joins as taking three steps. Step one is to get all the rows that would be retrieved as an inner join; step two is to get all the rows in the preserved (driving) table that weren't returned in step one; and step three is to test the returned rows against predicates in the WHERE clause. That's why adding qualifications for the preserved table in the ON clause does not prevent rows that don't meet the criteria from being returned: rows might be suppressed in step one, but they're added back in step two.

Once step two is done, WHERE predicates can be applied. So in your second query, you could just add "WHERE t1.x<5" to the main query and you'd be set.

Duke's Question

Duke Ganote, February 01, 2006 - 2:25 pm UTC

I'm not suggesting this is authoritative; however, the example provided here on ANSI outer joins
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/12475
in the section called "Predicate restrictions on an outer table" suggests the following behavior, if I'm reading it correctly.  

As Dave wrote: "All rows in the preserved table are preserved".  However, an ON predicate for the outer (or row-preserved) table restricts which preserved rows will be used to join to the inner (or null-extendable) table.  Specifically "Rows from the outer table that do not meet the condition are null-extended".

For example, if I change the ON predicate from "t1.X > 5" to "t1.X < 5", then none of the preserved rows satisfy both the ON conditions:

select t1.*, t2.any_other_column
  from t1 LEFT OUTER JOIN t2
       ON ( t1.x = t2.x
        AND <b>t1.X > 5</b> )
SQL> /

    X SOME_COLUMN ANY_OTHER_COLUMN
----- ----------- ----------------
    1         1.5
    2         2.5
    3         3.5
    4         4.5
    5         5.5

or here's another example limiting which rows are actually joined:

select t1.*, t2.any_other_column
  from t1 LEFT OUTER JOIN t2
       ON ( t1.x = t2.x
        AND <b>t1.X between 1.5 and 2.5</b> )
/

    X SOME_COLUMN ANY_OTHER_COLUMN
----- ----------- ----------------
    1         1.5
    2         2.5              3.5
    3         3.5
    4         4.5
    5         5.5 

Steve Spencer, February 21, 2006 - 9:52 pm UTC

I also did not find in the Oracle 9i doco any information about using AND within the ON clause of the join...but now I know so that's good.

One reason I went to an ANSI outer join was because I was trying to restrict the rows in the deficient table with this restriction:

AND lup.attribute1(+) IN ('U','B')

but Oracle does not allow the (+) and IN together.

The ANSI method works a treat.
(Someone told me that instead, I could use an in-line view intead of the deficient table and filter that view down...however I didn't want to complicate matters unneccessarily.)


Tom Kyte
February 22, 2006 - 8:33 am UTC

inline views don't complicate, in general, they greatly simplify

please clarify

Kumar, March 09, 2006 - 2:37 am UTC

Hi Tom,
which join condition for the following query is best

1) select ename,dname
from emp e,dept d
where e.deptno = 10 and e.deptno = d.deptno

2) select ename,dname
from emp e,dept d
where e.deptno = d.deptno and where e.deptno = 10

I hope we have to first join the rows and then do a
restrict..

Am I right or wrong?

Please clarify.
Bye!



Tom Kyte
March 09, 2006 - 1:13 pm UTC

a is best
b is better
wait - C is best

they are the same. the cost based optimizer doesn't really care about the order.

How to use new syntax on multiple tables

MM, March 10, 2006 - 9:03 am UTC

Hi Tom
Thanks for your time.

I am trying to use the 'new' syntax for outer join. I need to join 3 tables. I have a query like ( for simplicity sake I am using * for columns)
select table_a.* , table_b.*
from table_a left outer join table_b on
table_a.key1 = table_b.key1,
table_a left outer join table_c on
table_a.key2 = table_c.key2
where table_a.key1 = my_value;
I am getting the error that table_a.key1 in the predicate is abiguously defined. I can understand that there are 2 references of table_a in the from clause. But it is the same table_a (not like a self join). How do I represent that in this query? Is it even possible to do this?
If I used the old syntax I would have done something like
select table_a*, Table_b.*
from table_a, table_b
where table_a.key1 = table_b.key1(+)
and table_a.key2 = table_b.key2(+);
Does it even matter what syntax I use?
Since oracle documentation suggest that I use the 'New' syntax I am trying to rewrite my query.
Thank You


Tom Kyte
March 10, 2006 - 12:35 pm UTC

',' does not belong in the from list

1 select a.empno, b.empno, c.empno
2 from emp a left outer join emp2 b
3 on a.empno = b.empno
4 left outer join emp3 c
5* on a.empno = c.empno
scott@ORA10GR2> /

EMPNO EMPNO EMPNO
---------- ---------- ----------
7566 7566
7654 7654
7698 7698
7782 7782
7844 7844
7876 7876
7900 7900
7902 7902
7934 7934
7788 7788
7839 7839
7521 7521
7499 7499
7369 7369

14 rows selected.




ANSI Systex alternative

A Reader, April 12, 2006 - 1:36 am UTC

4 tables have some attribute about say a territory

table-a (terr, col1)
table-b (terr, col2)
table-c (terr, col3)
table-d (terr, col4)

desired output
terr col1 col2 col3 col4
america 5 - - 100
germany - - - 200
...

the usual way i have used

create table x as
select terr from table-a
UNION
select terr from table-b
UNION
select terr from table-c
UNION
select terr from table-d

and the L-OJ x to table-a, table-b, table-c, table-d

select x.terr,a.col1,b.col2.c.col3.d.col4
from (x x left outer join table-a on x.terr - table-a.terr)
left outer join table-b on x.terr - table-b.terr
... etc

is there a way to avoid the union table with all the features of ANSI systax.

If so is it advisable in your view from the performance and readabilty aspect.

Thaks as always !



Tom Kyte
April 12, 2006 - 8:07 am UTC

ugh, data model gone mad.

Ok, is there a table there that has ALL OF THE TERRITORIES or might terr=America appear in only one of a,b,c,d - but we don't know which one?

Or do you have a table with all of the possible territory values we can use to start with

And would you consider fixing the model?

Clarification on the Outer Join Post

A Reader, April 12, 2006 - 10:05 am UTC

Hi Tom,

Territory was just an example - and i recon a bad one - as something like territory - its easy to have a 'Master List'

But there are situations where its not easy to have a master list.

Lets say the four tables are being loaded based on feeds from external sources and are say keyed on Country_Code, Passport Numbers, its probably not possible to have a master file with all possible international passport numbers ...

I was looking for a solution more in those situations.

Union works but i think ANSI SQL should have a better/cleaner option of doing this.

Thanks

Tom Kyte
April 12, 2006 - 11:18 am UTC

No, there are no situations in a properly modeled database that enforces entity and data integrity whereby there would not be a master list. It is a design choice you make and live with.

You have the set of all passport numbers of interest in your system, you chose to store them in 4 tables.

If you don't have a "master list", you would be looking at using FULL OUTER JOINS between all tables since you have no idea where the unique set of passport numbers is.

UNION is ANSI SQL

what is "unclean" (union all is probably what you mean to use) about it?


It looks to me like....

Jon Waterhouse, April 12, 2006 - 10:28 am UTC

to follow your example:

For some reason your model has several passport tables,

say

PassportA has Country,Number,HairColour,Height

and PassportB has Country,Number,FingerprintID

etc.

And you really are trying to produce a report on All_Passports

which is a UNION ALL of PassprtA, PassPortB and any other passport types.

So rather than thinking of how to construct your rows and columns separately, you just need a VIEW that does the UNION ALL, and then produce your report on that.

ANSI Join performance

Ray, April 27, 2006 - 12:10 pm UTC

Hi Tom,

A was having a discussion with one of my collegues who seems to suggest that ANSI joins would make the execution plan more predictive in terms of driving table and driven table. He is also of the opinion that ANSI syntax leads to better performance.

Is this true? Or is it the outcome from people having worked on other rdbms have a preference for ANSI syntax?

I think that new ANSI syntax was just a conformation to the ANSI standard, which will also make migration from other rdbms to Oracle much easier.

Your opinion will be very helpful. Thanks in advance.

Regards
Ray

Tom Kyte
April 27, 2006 - 3:28 pm UTC

wow, why does he believe that?

a) faith
b) some sort of examples that demonstrate this belief?

I would believe in B) and would be willing to look at B)

If they have b) pony it up.

Left outer join

Thirumaran, May 02, 2006 - 6:36 am UTC

Hi Tom,

My collegues were commenting using a left outer join with two views is not advisable, moreover they are recommending not to use left outer join.

is this true ?

Thanks
Thirumaran

Tom Kyte
May 02, 2006 - 7:32 am UTC

a right outer join is so much faster ;)

only kidding.


The only sensible advice is simply:

"Outer join when you must, if you do not need to outer join, don't do it (of course)"


Ask them "so.... how would you avoid this left outer join (making it a right outer join doesn't count!) if I actually need the results produced by the outer join - and NOT the result of an inner join?"

"of two views" - or "of two things" - I don't really care. If the two views are the BEST VIEWS to be used - then ask them "what else would I do"

If the answer is "code a third view that incorporates the first two views logically" - they might well be right - but we are not discussing outer joins anymore - we are rather discussing using the right view at the right time in the right place.

LEFT OUTER JOIN

Duke Ganote, May 03, 2006 - 8:51 am UTC

Sounds like the crusty Oracle DBA (he then had 20 years experience) I met back in 1996 when I started working with Oracle databases. He was adamant that outer joins be performed at the application level because the database didn't perform them well. If he's still there, he probably hasn't changed his opinion yet.

Assuming there's a good business reason for the outer join, it has to be performed somewhere, and the database is the logical place to do data manipulations.

Documentation bug?

A reader, May 15, 2006 - 12:29 pm UTC

How does one create a documentation "bug" to ask for further examples where to put filtering conditions when using ANSI syntax:

select count(*)
from user_tables T1
inner join
user_tables t2
ON t1.table_name = t2.table_name
and t1.table_name > 'B'
and rownum < 10

is the same as :

select count(*)
from user_tables T1
inner join
user_tables t2
ON t1.table_name = t2.table_name
WHERE t1.table_name > 'B'
and rownum < 10


Tom Kyte
May 15, 2006 - 3:20 pm UTC

iTar's

but this one is simple:

a) join conditions in the ON
b) predicates in the WHERE

because putting the conditions in the ON statement will result in different results with different join types (outer joins)

Question on outer join "on" clause - filters on parent table vs. child table

Moorthy Rekapalli, August 24, 2006 - 11:48 am UTC

Tom,

First of all, thank you very much for your service to the Oracle community.  Let me dive into the question by setting up a test case.

create table parent
(parent_id    integer primary key
,parent_name    varchar2(30) not null
);

create table child
(child_id    integer primary key
,child_name    varchar2(30) not null
,parent_id    integer references parent(parent_id)
);

On the child side, it is (0,1 or N) relationship.

insert into parent(parent_id, parent_name) values (1, 'A');
insert into parent(parent_id, parent_name) values (2, 'B');
insert into parent(parent_id, parent_name) values (3, 'C');

insert into child(child_id,child_name,parent_id) values(1, 'A1', 1);
insert into child(child_id,child_name,parent_id) values(2, 'B1', 2);
insert into child(child_id,child_name,parent_id) values(3, 'B2', 2);
insert into child(child_id,child_name,parent_id) values(4, 'B3', 2);

commit;

column parent_name format a5
column child_name  format a5

Following query is properly filtering on child table as I don't see a row that corresponds to B3.

SQL> select p.parent_id, p.parent_name, c.child_id, c.child_name
  2    from parent p left outer join child c
  3    on   (p.parent_id = c.parent_id and (c.child_name in ('A1','B1','B2')) );

 PARENT_ID PAREN   CHILD_ID CHILD
---------- ----- ---------- -----
         1 A              1 A1
         2 B              2 B1
         2 B              3 B2
         3 C

My intention is to get the attributes from child table (if a corresponding record exists) for parent_name of 'A' and it is working fine when I give the filter on parent table in "where" clause.

SQL> select p.parent_id, p.parent_name, c.child_id, c.child_name
  2    from parent p left outer join child c
  3         on (p.parent_id = c.parent_id)
  4   where p.parent_name = 'A';

 PARENT_ID PAREN   CHILD_ID CHILD
---------- ----- ---------- -----
         1 A              1 A1

Now, my question is when I added the filter on parent table as part of "on" clause, I get all the records that are in parent. Why is that?  Is this stated behaviour for "on" clause?  If so, it can potentially create lot of logical bugs in the code if developers don't know this little detail.

SQL> select p.parent_id, p.parent_name, c.child_id, c.child_name
  2    from parent p left outer join child c
  3         on (p.parent_id = c.parent_id and p.parent_name = 'A');

 PARENT_ID PAREN   CHILD_ID CHILD
---------- ----- ---------- -----
         1 A              1 A1
         2 B
         3 C

SQL> select p.parent_id, p.parent_name, c.child_id, c.child_name
  2    from parent p left outer join child c
  3         on (p.parent_id = c.parent_id and p.parent_name = 'B');

 PARENT_ID PAREN   CHILD_ID CHILD
---------- ----- ---------- -----
         1 A
         2 B              2 B1
         2 B              3 B2
         2 B              4 B3
         3 C

Thanks,
Moorthy. 

Tom Kyte
August 27, 2006 - 7:58 pm UTC

to "predicate" on the parent table, you want to use a where clause - else by DEFINITION - every row in the parent table is coming out.

If you:

select ...
from t1 left outer join t2 on (anything)
/

you get BY DEFINITION everything from t1, and if something matched the "on" clause - stuff from t2 will be there (if not, t2 is "made up")



Back to the Basics for a sec.....

klabu, October 16, 2006 - 11:57 am UTC

Assuming you know NOTHING about these 3 tables - don't know about the DATA -beyond the FK & PK and they're 100% valid ref. integerity

select *
from A, B, C
where A.fk = C.pk
and B.fk = C.pk

Is this JOIN "complete" ?
or an additional predicate is needed between A and B ?

If you can not be certain...what other info do you need to determine ?

How can you tell if the above SQL produces a Cartesian result ?

thanks



Tom Kyte
October 16, 2006 - 12:53 pm UTC

it is complete - but probably wrong.

this is complete in fact:

select * from a, b, c;


as are all other variations. they are all complete.


the question should be "are they correct"

for which, to answer, we'd need to know the question and how the tables relate to eachother.


most parent/child are one to many and optional - that is why I'll say it is "complete" but probably wrong.

Dept has many Emps (dept = C, emp = A)
Dept has many projects (dept = c, projects = B)


if you do the above query for a dept with 50 employees and 20 projects, you'll end up with 50*20 = 1000 rows for that dept as you cartesian join EMP with PROJECTS. Since there is no way to really "join" emp with projects - the answer is "complete" but probably not "useful".

if you do the above query for a dept with 500 employees, but no projects, you'll get zero rows. Complete, but not likely correct.


and so on.

ANSI Compliance

Ashutosh, January 02, 2007 - 10:47 am UTC

Hi Tom,

I really hope the good old A=B(+) join syntax will retained by Oracle in its future releases. From the pages on your site, I can plainly see the confusion wrought by the new ANSI syntax. Being an old Oracle hand myself, I can fully sympathize with the likes of me. I fully understand your need for ANSI compliance as far as the join syntax is concerned. My question is - does that compliance mandate doing away with old non-compliant features? Can you retain these AND the ANSI compliant features in parallel?

Thanks a ton for your time.

Tom Kyte
January 04, 2007 - 9:41 am UTC

It would be a long long time before any old syntax would be removed - backwards compatibility is something they strive for, especially in the language.

ANSI syntax no safeguard for inadvertent Cartesians

Duke Ganote, May 02, 2007 - 5:33 pm UTC

ANIS syntax doesn't guarantee no inadvertent Cartesian joins; I just pulled this typo:
create table t as select level t from dual connect by level < 5;
create table u as select level u from dual connect by level < 5;
select count(*) from t inner join u on ( t.t = t.t );

 COUNT(*)
---------
       16

well, duh. I typoed the join condition: no condition on the 2nd table! It should have been:
select count(*) from t inner join u on ( t.t = u.u );

 COUNT(*)
---------
        4

Diffence b/w Union All and Full Outer Join

amit, November 22, 2007 - 3:24 am UTC

Thanks for your input!!
Appreciate, if you can explain difference between Union All and ANSI Full Outer Join by example?

Tom Kyte
November 26, 2007 - 10:33 am UTC

a union all and a full outer join are about as different as you can get.

It is hard to know where to begin to explain the "difference" between

a) union all which concatenates two result sets - select * from T1 UNION ALL select * from T2 = T1 in its entirety PLUS by T2 in its entirety.

b) full outer join joins T1 and T2 such that if T1 and T2 have "mates", "matches" they are represented in the output - and if there is a row(s) in T1 that don't have any mates in T2 - they are output with null values for T2 columns and if there is a row(s) in T2 that don't have mates in T2 - they are output with null values for T1 columns.

Re: Diffence b/w Union All and Full Outer Join

Laurent Schneider, January 11, 2008 - 5:07 am UTC

A difference is that in 11g the table is selected only once :

select * from emp full outer join dept on (emp.deptno=dept.deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 1333064428

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    15 |  1755 |     7  (15)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |    15 |  1755 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |    15 |   855 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT     |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | EMP      |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Tom Kyte
January 11, 2008 - 7:43 am UTC

yes, that is a new 11g query plan optimization...

see
http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

bhagyasree nallagangu, May 05, 2008 - 10:45 am UTC

Hi TOM,

 I was reading all about joins and couldnt figure out how to join my tables...i have 3 tables

SQL> desc assembly_parts
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ASSB_PT_NBR_SEQ_ID                      NOT NULL NUMBER(8)
 DML_TS                                  NOT NULL DATE
 DML_USER_ID                          NOT NULL VARCHAR2(30)
 BRAND_ID                             NOT NULL VARCHAR2(4)
 ASSEMBLY_PART_NBR                    NOT NULL VARCHAR2(35)
 ASSB_MFG_ORG_SEQ_ID                           NUMBER(8)
 ASSB_PROD_ORG_SEQ_ID                          NUMBER(8)
 PROD_CODE                                     VARCHAR2(4)
 ALT_MFG_BILL_OF_MATERIAL_NBR                  NUMBER(2)
 PART_DESC                                     VARCHAR2(35)
 PROD_TRADEMARK_ID                             VARCHAR2(30)
 PRODUCT_NM                                    VARCHAR2(70)
 MANUFACTURING_LOCATION_ID            NOT NULL VARCHAR2(2)

PK  ASSB_PT_NBR_SEQ_ID     

FK    BRAND_ID
      ASSB_MFG_ORG_SEQ_ID    
      ASSB_PROD_ORG_SEQ_ID   
      MANUFACTURING_LOCATION_ID



SQL> desc ps_operations
 Name                                      Null?    Type
 ----------------------------------------- -------- 
 ASSB_PT_NBR_SEQ_ID                    NOT NULL NUMBER(8)
 OPERATION_NBR                        NOT NULL VARCHAR2(10)
 EFFECTIVE_FROM_DT                    NOT NULL DATE
 DML_TS                               NOT NULL DATE
 DML_USER_ID                          NOT NULL VARCHAR2(30)
 OPERATION_DESC                       NOT NULL VARCHAR2(70)
 HOURS_PER_PIECE_QTY                  NOT NULL NUMBER(9,6)
 PIECES_PER_HOUR_RATE_QTY             NOT NULL NUMBER(15,7)
 EFFECTIVE_TO_DT                      DATE
 EXTRACT_IND                          VARCHAR2(1)

composite primary key  ASSB_PT_NBR_SEQ_ID
                       OPERATION_NBR     

FK           ASSB_PT_NBR_SEQ_ID



SQL> desc assembly_part_details
 Name                                      Null?    Type
 ----------------------------------------- -------- -------
 ASSB_PT_NBR_SEQ_ID                   NOT NULL NUMBER(8)
 EFFECTIVE_FROM_DT                    NOT NULL DATE
 DML_TS                               NOT NULL DATE
 DML_USER_ID                          NOT NULL VARCHAR2(30)
 EXPORT_SCHEDULE_B_CLASS_CDE                   VARCHAR2(12)
 NAFTA_BASIS_CDE                               VARCHAR2(1)
 EFFECTIVE_TO_DT                               DATE
 GENERIC_PART_DESC                             VARCHAR2(35)
 MARKING_WAIVER_IND                            VARCHAR2(1)
 MARKING_WAIVER_DT                             DATE
 ASSEMBLY_TOTAL_COST_AMT                       NUMBER(13,6)
 ASSEMBLY_FOREIGN_COST_AMT                     NUMBER(13,6)
 NAFTA_PART_ORIGIN_IND                         VARCHAR2(1)
 NAFTA_DETERMINATION_TYPE_CDE                  VARCHAR2(1)
 PCODE                                         VARCHAR2(17)
 EXTRACT_IND                                   VARCHAR2(1)
 TOTAL_OVERALL_COST_AMT                        NUMBER(14,6)
 DUTY_MATERIAL_COST_AMT                        NUMBER(14,6)
 NON_DUTY_MATERIAL_COST_AMT                    NUMBER(14,6)
 NON_DUTY_PACKAGING_COST_AMT                   NUMBER(14,6)
 DIRECT_LABOR_COST_AMT                         NUMBER(14,6)
 DIRECT_LABOR_PER_HOUR_RATE                    NUMBER(5,2)
 COSTING_DT                                    DATE
 COST_ERROR_IND                                VARCHAR2(1)
 OVERHEAD_RATE_AMT                             NUMBER(6,3)
 TOTAL_OVERHEAD_COST_AMT                       NUMBER(14,6)

composite primary key  ASSB_PT_NBR_SEQ_ID     
                       EFFECTIVE_FROM_DT    

FK      ASSB_PT_NBR_SEQ_ID            
        NAFTA_BASIS_CDE
        NAFTA_PART_ORIGIN_IND
        NAFTA_DETERMINATION_TYPE_CDE  
        EXPORT_SCHEDULE_B_CLASS_CDE     

I tried many ways to join these tables but they are not working....
Please help me out and do explain me how i should join if i have CPK's
My query
select a2.assembly_part_nbr as Assembly_Part_NBR, 
sum(p.hours_per_piece_qty) As Total_hrs_per_piece, 
a2.part_desc||' '||'SAP' as Part_DESC
from ps_operations p , 
assembly_part_details a1, 
assembly_parts a2
where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id  AND
to_char(p.effective_to_dt) is null
group by  a2.assembly_part_nbr, a2.part_desc

please explain me where i was wrong and why its not working...

Thanks a lot

Sree

Tom Kyte
May 05, 2008 - 2:46 pm UTC

.. I was reading all about joins and couldnt figure out how to join my tables...i
...

the sad thing is, you should not have to read anything beyond your data dictionary or your system specs.

In all honesty, this one - this one question - has me feeling sadder about the state of our industry than I ever have in a long long time.


maybe if we knew what the foreign keys were to.... Like, why do all three tables have foreign keys listed????? Only two of the three would...

if you look at your "list"

SQL> desc assembly_parts

PK  ASSB_PT_NBR_SEQ_ID     

FK    BRAND_ID
      ASSB_MFG_ORG_SEQ_ID    
      ASSB_PROD_ORG_SEQ_ID   
      MANUFACTURING_LOCATION_ID

SQL> desc ps_operations
composite primary key  ASSB_PT_NBR_SEQ_ID
                       OPERATION_NBR     

FK           ASSB_PT_NBR_SEQ_ID


SQL> desc assembly_part_details

composite primary key  ASSB_PT_NBR_SEQ_ID     
                       EFFECTIVE_FROM_DT    

FK      ASSB_PT_NBR_SEQ_ID            
        NAFTA_BASIS_CDE
        NAFTA_PART_ORIGIN_IND
        NAFTA_DETERMINATION_TYPE_CDE  
        EXPORT_SCHEDULE_B_CLASS_CDE   


it looks like - STRESS LOOKS LIKE because you don't say what table that fkey for the second table is even FOR - that
SQL> desc assembly_parts
PK  ASSB_PT_NBR_SEQ_ID     
<b>might one to many join to</b>
SQL> desc ps_operations
FK           ASSB_PT_NBR_SEQ_ID



MAYBE - but beyond that - no clue for you. You have a third table with a 4 part foreign key - but no table with four attributes in the primary key.


That in the year 2008 we are asking, no - pleading - for how to "join my tables together" makes me cringe.


Looking at your table names however leads me to believe you might need two queries - as you appear to have two one to many relations.

But that won't mean anything to you - so the only thing I can suggest is you sit down with someone where you work and "ask them for help, to teach you, to mentor you"

Can I use new join syntax with self join?

David Wellborn, June 13, 2008 - 6:53 pm UTC

I'm trying to use a left outer join using a self join.

CREATE TABLE TEST (ID NUMBER, 
                   company VARCHAR2(1),
                   project NUMBER,
                   value1 NUMBER,
                   value2 NUMBER);

INSERT INTO TEST
            (ID, company, project, value1, value2
            )
     VALUES (1, 'A', 1, 10, 20
            );
INSERT INTO TEST
            (ID, company, project, value1, value2
            )
     VALUES (2, 'A', 2, 100, 200
            );
INSERT INTO TEST
            (ID, company, project, value1, value2
            )
     VALUES (3, 'B', 1, 1000, 2000
            );

SELECT NVL (a.company, b.company) company, a.value1 p1_val1, b.value1 p2_val1,
       a.value2 p1_val2, b.value2 p2_val2
  FROM TEST a LEFT OUTER JOIN TEST b
       ON a.company = b.company AND a.project = 1 AND b.project = 2;

When it runs:
Table created.

1 row created.
1 row created.
1 row created.

C    P1_VAL1    P2_VAL1    P1_VAL2    P2_VAL2
- ---------- ---------- ---------- ----------
A         10        100         20        200
A        100                   200
B       1000                  2000



Why is there a second row for A? How can I get rid of it?

Tom Kyte
June 16, 2008 - 11:30 am UTC

It is the VERY DEFINITION of the outer join. Look you

(first_table) left outer join (second_table)


If you query the first table:

ops$tkyte%ORA10GR2> select * from test;

        ID C    PROJECT     VALUE1     VALUE2
---------- - ---------- ---------- ----------
         1 A          1         10         20
         2 A          2        100        200
         3 B          1       1000       2000



you get three rows. therefore, your output of (first) left out join (second) will have at least three rows - all of the rows from first will be present in the output

Not sure where the confusion comes from - you put all of the where clauses in the ON (outer join bit), that'll not filter first table, only second table...


did you mean to use a WHERE clause?

ops$tkyte%ORA10GR2> SELECT NVL (a.company, b.company) company, a.value1 p1_val1, b.value1 p2_val1,
  2         a.value2 p1_val2, b.value2 p2_val2
  3    FROM (select * from TEST where project = 1) a LEFT OUTER JOIN (select * from TEST where project = 2) b
  4         ON a.company = b.company ;

C    P1_VAL1    P2_VAL1    P1_VAL2    P2_VAL2
- ---------- ---------- ---------- ----------
A         10        100         20        200
B       1000                  2000

Cartesian product

Amit, July 11, 2008 - 5:28 am UTC

This may sound fullish but an interview question..
what is cartesion product? (dont worry I answered that)
I want is "How to remove the cartesion product?"
Tom Kyte
July 11, 2008 - 8:18 am UTC

depends on what editor you use.

In vi, I would use

dG to remove the current line on down in the file

or dd on the single line to remove just the from clause.




think about this, you do not "remove" something like that. You either

a) need it
b) made a mistake and fix the query


It is a question that cannot be answered logically, it can only be answered with a quizzical look on your face...


It is sort of like asking "how can I remove select" - you either need to do a cartesian join - in which case removing it doesn't make sense, or you don't need to do a cartesian join, in which case the original query is in error, should be erased and coded correctly.

Cartesian Product

Jimmy, July 11, 2008 - 11:32 am UTC

Tom, could you give a real-life example of where a cartesian product might be useful? I have never ran across a situation where I needed a cartesian product.
Tom Kyte
July 11, 2008 - 12:01 pm UTC

i have a table of dates - say 01-jan-2008 thru 07-jan-2008
i have a table of product_ids - say 1, 2, 3, 4

I have a table of inventory amounts

date           prod  qtr
01-jan-2008    1     1000
01-jan-2008    2     1000
01-jan-2008    3     1000
01-jan-2008    4     1000
03-jan-2008    2     500
04-jan-2008    4     250



I need to report for every day in the date table, for every product, the amount I have on hand.

Before 10g with a partitioned outer join, we needed to

(select * from dates, products)

to get every date with every product - then we would outer join to our inventory table

that is one example - they happen, they are rare.


another case, I want to turn columns into rows.... Before 11g with UNPIVOT, I would

select empno, decode(r,1,ename,2,to_char(hiredate),3,to_char(sal)
  from emp, (select level r from dual connect by level <= 3)


to turn every row into three rows and print them down the page.

natural join bug ?

Sokrates, July 23, 2008 - 7:38 am UTC

10.2.0 > create table x(
  2  m integer,
  3  s integer
  4  );

Table created.

10.2.0 >
10.2.0 > create table y(
  2  m integer
  3  );

Table created.

10.2.0 >
10.2.0 >
10.2.0 > create view c as
  2  select * from x natural join y;

View created.

10.2.0 >
10.2.0 > insert into y(m) values(1);

1 row created.

10.2.0 > insert into x(m) values(1);

1 row created.

10.2.0 >
10.2.0 >
10.2.0 > select * from c;

         M          S
---------- ----------
         1

10.2.0 > select s from c;
select s from c
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

?

Tom Kyte
July 24, 2008 - 10:21 am UTC

yes it would appear so, if you do not use * in the view definition, it is OK (I would suggest not using * in any and all real code anyway...)

Natural join bug fixed in 11g

Gary, July 24, 2008 - 8:27 pm UTC

Sokrates's natural join bug doesn't show up in 11.1.0.6 (Windows Std Edition), so it has been fixed. Don't know if there is a patch for 10gR2 for it though.
But not only wouldn't I use a * in production code, I wouldn't use a natural join anyway. If you are going to specify the columns you want, you should certainly specify the ones you are joining on too.

thanks

Sokrates, July 25, 2008 - 2:32 am UTC

Tom and Gary

of course, using * in prodcode is a sin.
I think I tend to agree with Gary that using natural join in production is also a sin -> that's what I learnt here !

by the way:
select text from user_views where view_name='C';

TEXT
--------------------------------------------------------------------------------
select * from x natural join y


vs.
create view d as
select x.* from x,y where x.m=y.m;
and
select text from user_views where view_name='D';

TEXT
--------------------------------------------------------------------------------
select x."M",x."S" from x,y where x.m=y.m


seems to be a candidate for the reason of this bug:
in case a "normal join" is used to create the view, "*" is resolved into the columns (as of creation time) to store the view definition
whereas in case of a "new natural join", "*" itself is stored in the view definition
(just a suspicion)

Original and New Implementation for Nested Loop Joins

A reader, September 28, 2010 - 1:05 pm UTC

Original and New Implementation for Nested Loop Joins
Sir,

I have been reading the topic here:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#i36235

Can you please explain the NEW METHOD some more easy way to understand ? and what are the benefits over the old method of implementation of nested loops ?
Tom Kyte
September 28, 2010 - 1:20 pm UTC

why should it be easier to understand? It is more complex actually - it is written to make IO more efficient - not to make our lives as humans trying to understand stuff easier :)

It is a more complex algorithm that permits more efficient IO, the original implementation was much less complex in design.

nidhi, January 29, 2011 - 1:01 am UTC

hi.................

create view invcountmember (mem_id,nInv)
as SELECT Invoice.mem_id,count(*) as nInv
from invoice
group by invoice.mem_id;

create view dismembers(mem_id)
as select mem_id from invcountmember
inner join movallowed on invcountmember.ninv=movallowed.num
where invcountmember.ninv>=movallowed.num;
after typeng its showing sql command is not proply ended pls help m soon

Tom Kyte
February 01, 2011 - 3:44 pm UTC


Perhaps if you posted an entire example, I could comment.

ops$tkyte%ORA11GR2> create table invoice ( mem_id number );

Table created.

ops$tkyte%ORA11GR2> create table movallowed (num number );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create OR REPLACE view invcountmember (mem_id,nInv)
  2  as SELECT Invoice.mem_id,count(*) as nInv
  3  from invoice
  4  group by invoice.mem_id;

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create OR REPLACE view dismembers(mem_id)
  2  as select mem_id from invcountmember
  3  inner join movallowed on invcountmember.ninv=movallowed.num
  4  where invcountmember.ninv>=movallowed.num;

View created.

about query

satish, May 07, 2011 - 8:21 pm UTC

hi tom,
when i gave select * from emp was not displayed where as select * from emp where eid=10 was displayed then what happens can u explain....plzz
Tom Kyte
May 09, 2011 - 7:10 am UTC

I have NO clue what you mean, none at all.


And your keyboard is broken. Who or what is this "U" you are talking about?

2 INNER JOINS

Abhijit, May 20, 2011 - 8:05 am UTC

Hello Tom,
I would like to know how to join one table with one or more tables using the ANSI compliant INNER JOIN syntax.
My query has three tables and I am attempting to join the first table with the other two.

I have been successful using the syntax I am familiar with.

CREATE TABLE VEHICLES(VEHICLE_ID INT PRIMARY KEY, VEHICLE_NAME VARCHAR2(100));

CREATE TABLE VEHICLE_OWNERS(VEHICLE_ID INT, OWNER_NAME VARCHAR2(100));

CREATE TABLE VEHICLE_PLATES(PLATE_ID INT, PLATE_DESC VARCHAR2(100), VEHICLE_ID INT);

INSERT INTO VEHICLES VALUES(100, 'HYUNDAI TUCSON');
INSERT INTO VEHICLES VALUES(200, 'TOYOTA CAMRY');

INSERT INTO VEHICLE_OWNERS VALUES(100, 'JOHN SMITH');
INSERT INTO VEHICLE_OWNERS VALUES(200, 'JANE SMITH');

INSERT INTO VEHICLE_PLATES VALUES(200,'HAG-2101',200)


SELECT * FROM VEHICLES V, VEHICLE_OWNERS VO, VEHICLE_PLATES VP
WHERE V.VEHICLE_ID = VO.VEHICLE_ID
AND V.VEHICLE_ID = VP.VEHICLE_ID


How do I change the last query so that I can demonstrate the use of an INNER JOIN.

Note: This is for academic purposes only.
Tom Kyte
May 23, 2011 - 11:29 am UTC

ops$tkyte%ORA11GR2> select *
  2    from vehicles v inner join vehicle_owners vo
  3         on (v.vehicle_id = vo.vehicle_id)
  4             inner join vehicle_plates vp
  5             on (v.vehicle_id = vp.vehicle_id )
  6  /

VEHICLE_ID
----------
VEHICLE_NAME
-------------------------------------------------------------------------------
VEHICLE_ID
----------
OWNER_NAME
-------------------------------------------------------------------------------
  PLATE_ID
----------
PLATE_DESC
-------------------------------------------------------------------------------
VEHICLE_ID
----------
       200
TOYOTA CAMRY
       200
JANE SMITH
       200
HAG-2101
       200


I think I found the answer

Abhijit, May 20, 2011 - 8:09 am UTC

Tom,
I believe I have found the answer.


       
SELECT *
  FROM (Vehicles v INNER JOIN Vehicle_Owners Vo ON
        v.Vehicle_Id = Vo.Vehicle_Id)
 INNER JOIN Vehicle_Plates Vp
    ON (v.Vehicle_Id = Vp.Vehicle_Id)


Thanks.

Your Opinion/Expertise needed

Michael, July 13, 2012 - 1:58 am UTC

Hi Tom,

your expertise is needed:

Consider the "classic" emp/dept schema with a foreign key between emp.deptno and dept.deptno. But emp.deptno might be NULL... so to get all employees and there deptartment, we have to outer join.

This query fails with ORA-01445:
select rowid
from   (select e.empno
        from   emp e 
               left outer join (select deptno from dept) d on(e.deptno = d.deptno))

If you replace the inline view on dept with a normal view, the query will also fail with ORA-01445!

The following queries will work correctly, because they do NOT use simultaneously a view and a ANSI outer join:
select rowid
from   (select e.empno
        from   emp e, (select deptno from dept) d
        where  e.deptno = d.deptno (+))

select rowid
from   (select e.empno
        from   emp e left outer join dept d on(e.deptno = d.deptno))

I hope you can reproduce this - i get this on 11.2.0.3.
Is this excepted or a bug or ... something else?!?
If it is expected where is this difference documented? (I searched, but didn't find anything...)
Tom Kyte
July 16, 2012 - 1:49 pm UTC

I would say that potentially looks like a minor bug with the ANSI syntax and key preservation. Please file this test case with support and they can confirm and open it.

Support's response...

Michael, July 17, 2012 - 6:32 am UTC

Hi Tom,

thanks for your assessment.
I filed a SR for this issue (SR #3-5895081011) - well, but they say:

"Our development team was able reproducing the error in-house too and think this is expected behavior as the error is reported from a piece of code where querying ROWID is illegal. Here is their inquiry to you: "Why do you think this is a bug ? What ROW ID are you expecting here?"

I explained that i expect the EMPs ROWID and that - if that is NOT a bug - one would have to conclude that the ANSI outer join syntax is virtually useless, because queries/views using it with views are almost always not updatable...

I don't know how to explain this better - do you?
Tom Kyte
July 17, 2012 - 8:52 am UTC

tell them:

well, duh, we expect it to come from the key preserved table which is EMP clearly. The Oracle (+) outer join does it correctly, why doesn't the ANSI join do it?

one would have to conclude that the ANSI outer join syntax is virtually useless, because queries/views using it with views are almost always not updatable.

I would disagree with that conclusion however, it is a little too hyperbolic. It would be the rare case where this is true. You had to hide the table in an inline view.



You can refer them to bug #14336875

ANSI OUTER JOIN TO A SIMPLE INLINE VIEW RESULTS IN ORA-1445, (+) SYNTAX DOES NOT

to help the SR along.

Not only inline views...

Michael, July 17, 2012 - 9:26 am UTC

Hi Tom,

OK, my conclusion was a bit to exaggerated.

But unfortunately, not only inline views are affected, also simplest, normal (defined with DDL) views are affected if try to outer join them with ANSI - i just used an inline view to have a test case that is as small as possible.
Someone around here likes that ;-)

I have updated my SR with a reference to this thread...
Let's see if argueing from authority works here - Beggars can't be choosers.
Tom Kyte
July 17, 2012 - 9:32 am UTC

But unfortunately, not only inline views are affected, also simplest, normal (defined with DDL) views are affected if try to outer join them with ANSI

do you have an example?

Let's see if arguing from authority works here - Beggars can't be choosers.


that is exactly what we are doing :( I try to avoid that but when it doesn't work out, I'll do it...

Here's the example...

Michael, July 18, 2012 - 2:15 am UTC

Hi Tom,

here's an example with a real view:
create or replace view dept_v as 
select * from dept
/

Now let's try:
select rowid
from   (select e.empno
        from   emp e 
               left outer join dept_v d on(e.deptno = d.deptno))
/

Then i get an error:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Now you may ask: is there something strange with your database?
Well, i tested this on SQL Workshop on apex.oracle.com.
Can you reproduce this on your local db? If not, i could provide you with the workspace, username etc. to try it on apex.oracle.com yourself.

The db on apex.oracle.com seems to be up to date.
select banner from v$version
/
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Do you still think that my conclusion was "a little too hyperbolic"?
Btw, I think this issue is worth to get fixed asap, so a little bit arguing from authority is forgivable here...
Tom Kyte
July 18, 2012 - 9:46 am UTC

have them add that to the bug please - I see what you mean now - they totally lose the primary key anytime the table is hidden by any sort of view.

probably when they fix the inline view, this will also get fixed - but best make it part of the bug report


thanks!

Which one is best practice? join using + or using keyword

Austin, November 12, 2013 - 12:11 pm UTC

Hi Tom,
currently I am using Oracle 10g,but we have several queries which was written by using +(plus) and keyword(left/right).
My question here is,
1. Which one is best practtice? using + or using left/right while join tables?
2. Is there any performance differnce between them?

For eg.

select * from emp e right outer join dept d
on e.DEPTNO = d.DEPTNO;

select * from emp e,dept d
where e.DEPTNO(+) = d.DEPTNO;

select * from emp e left outer join dept d
on e.DEPTNO = d.DEPTNO;

select * from emp e,dept d
where e.DEPTNO = d.DEPTNO(+);

---------------------------------------

JOIN or where performance same?

Elvin, November 21, 2013 - 4:05 am UTC

Hi,

I have always worked on mysql and just shifted to new company where oracle is in use and they are not using joins instead selecting table in from and giving conditions in where clause when I asked my manager about performance cost she told me this will not affect perofrmance in oracle. But I am not satisfied can you please give your review on this.

Thank you

Instead of FULL OUTER JOIN

Pradeep, March 24, 2014 - 9:38 pm UTC

Hi Tom,

We have cursor query that is used in a package to compare rows of a target and a source table (Having identical columns). The query matches the rows in source and target tables. If there is an extra row in source it inserts row in target and if there is an extra row in target it updates the target column "diff_del_flag" to mark that as deleted. If both rows are found but there is data difference i.e the data has been updated in source table , then it updates the target table.
This is done by full outer join on a unique column termed as PK.

Query is :

SELECT s.ADDRESS_LINE_1 s_ADDRESS_LINE_1,s.ADDRESS_LINE_2 s_ADDRESS_LINE_2,s.ADDRESS_LINE_3 s_ADDRESS_LINE_3,s.AGREEMENT_LINE_ID s_AGREEMENT_LINE_ID,s.BUSINESS_RESPONSE_NAME s_BUSINESS_RESPONSE_NAME,s.CREATED_DATE s_CREATED_DATE,s.DMO_FLAG s_DMO_FLAG,s.END_DATE s_END_DATE,s.LICENCE_NUMBER s_LICENCE_NUMBER,s.POST_CODE s_POST_CODE,s.POST_TOWN s_POST_TOWN,s.RESPONSE_SERVICE_FLAG s_RESPONSE_SERVICE_FLAG,s.START_DATE s_START_DATE,s.UPDATED_DATE s_UPDATED_DATE
, s.pk s_pk
, t.ADDRESS_LINE_1 t_ADDRESS_LINE_1,t.ADDRESS_LINE_2 t_ADDRESS_LINE_2,t.ADDRESS_LINE_3 t_ADDRESS_LINE_3,t.AGREEMENT_LINE_ID t_AGREEMENT_LINE_ID,t.BUSINESS_RESPONSE_NAME t_BUSINESS_RESPONSE_NAME,t.CREATED_DATE t_CREATED_DATE,t.DMO_FLAG t_DMO_FLAG,t.END_DATE t_END_DATE,t.LICENCE_NUMBER t_LICENCE_NUMBER,t.POST_CODE t_POST_CODE,t.POST_TOWN t_POST_TOWN,t.RESPONSE_SERVICE_FLAG t_RESPONSE_SERVICE_FLAG,t.START_DATE t_START_DATE,t.UPDATED_DATE t_UPDATED_DATE
, t.pk t_pk
, t.diff_del_flag
, t.rowid t_rowid
FROM MBV_TEST s
FULL OUTER JOIN MBT_TEST t ON s.pk=t.pk
WHERE NOT (s.pk IS NULL AND t.pk IS NULL);


I am having some issues with the FULL OUTER JOIN. Is there any other way of writing this query instead FULL OUTER JOIN. I.e using UNIOUN etc....

Many Thanks !!

A reader, December 19, 2018 - 7:50 pm UTC

Hi masters,
Slightly stuck on the type of join to produce the right result...

Table A has some null values. I'd like to select from it based on some column. I'd like to then retain all those rows and add a value from another table (padding nulls with nulls)...

create table a (id number, str varchar2(10));
insert into a values (1, 'stringA');
insert into a values (2, 'stringB');
insert into a values (3, 'stringC');
insert into a values (4, 'stringC');
insert into a values (null, 'stringA');
insert into a values (null, 'stringC');

create table b (key number, whenn varchar2(10));
insert into b values (1, 'today');
insert into b values (1, 'tomorrow');
insert into b values (2, 'today');
insert into b values (3, 'tomorrow');
insert into b values (3, 'today');
insert into b values (3, 'tomorrow');
insert into b values (4, 'today');
insert into b values (4, 'tomorrow');

The expected result should be
ID   STR      KEY   WHENN
3    stringC  3     today
4    stringC  4     today
null stringC  null  null


Doing
select a.*, b.*
from a, b
where a.str = 'stringC' and b.whenn = 'today' and a.id = b.key(+);


only produces the first 3 rows... Tried it a few other ways, but that gives me way too many rows. The set should be purely driven based on selection from A (and then padded by B where available).

What is the right way of doing this?
Thank you!


Chris Saxon
December 20, 2018 - 5:38 pm UTC

You need to include the plus operator on all the columns of the table you're outer joining. Or in ANSI syntax, filter B in the join clause instead of where:

select a.*, b.*
from   a, b
where a.str = 'stringC' 
and   b.whenn (+) = 'today' 
and   a.id = b.key(+);

ID       STR       KEY      WHENN    
       3 stringC          3 today    
       4 stringC          4 today    
  <null> stringC     <null> <null> 

select a.*, b.*
from   a
left   join b
on     a.id = b.key
and    b.whenn (+) = 'today' 
where  a.str = 'stringC';

ID       STR       KEY      WHENN    
       3 stringC          3 today    
       4 stringC          4 today    
  <null> stringC     <null> <null>

Unclear syntax

Dieter, January 07, 2019 - 9:07 am UTC

In your latest example, you used this syntax:

SELECT a.*,
b.*
FROM a
LEFT JOIN b
ON a.id = b.key
AND b.whenn(+) = 'today'
WHERE a.str = 'stringC';

For me it is not clear, what purpose has "(+)" in an ANSI JOIN ...

TIA
Connor McDonald
January 08, 2019 - 1:35 am UTC

I think that's just a typo. The results are the same

SQL> SELECT a.*,
  2  b.*
  3  FROM a
  4  LEFT JOIN b
  5  ON a.id = b.key
  6  AND b.whenn(+) = 'today'
  7  WHERE a.str = 'stringC';

        ID STR               KEY WHENN
---------- ---------- ---------- ----------
         3 stringC             3 today
         4 stringC             4 today
           stringC

SQL> SELECT a.*,
  2  b.*
  3  FROM a
  4  LEFT JOIN b
  5  ON a.id = b.key
  6  AND b.whenn = 'today'
  7  WHERE a.str = 'stringC';

        ID STR               KEY WHENN
---------- ---------- ---------- ----------
         3 stringC             3 today
         4 stringC             4 today
           stringC


It's interesting that the SQL engine did not throw an error here. A 10053 trace shows that the query was rewritten to

SELECT "from$_subquery$_003"."ID_0" "ID","from$_subquery$_003"."STR_1" "STR","from$_subquery$_003"."KEY_2" "KEY","from$_subquery$_003"."WHENN_3" "WHENN" FROM  (SELECT "A"."ID" "ID_0","A"."STR" "STR_1","B"."KEY" "KEY_2","B"."WHENN" "WHENN_3" FROM "MCDONAC"."A" "A","MCDONAC"."B" "B" WHERE "A"."ID"="B"."KEY"(+) AND "B"."WHENN"(+)='today') "from$_subquery$_003" WHERE "from$_subquery$_003"."STR_1"='stringC'




join query with on clause cost is high than ansi join with where clause

Praveen Darla, May 17, 2019 - 8:31 am UTC

ANSI join query cost is 3386462602 and join clause query cost is 17967881751.

Hi Tom,
I re write the join query with where clause to on clause for performance but the cost of the query is very high compared to before , can you please assist some solution to increase the performance of the join query .

Thanks,
Praveen Darla.



Chris Saxon
May 17, 2019 - 9:10 am UTC

You're going to have to share your queries and their execution plans for us to help you out here!