Unexplained/strange behavior of a join query

Question

select d.department_id "Department Nummber", department_name,
       nvl(last_name, 'NoManager!') "Manager",
       job_id

from departments d left outer join employees e on (d.manager_id=e.employee_id)
     natural join jobs j

order by 1;

I can't figure out the result of the above query, which returns 11 rows when executed.. . The mystery is that the very same query returns 513 rows when "job_id" is removed from the select list or the whole list is replaced with count(*) where the returned count of rows is also 513.

I believe it's the natural join that's causing the unexplaindly varying results, as replacing

natural join jobs

with

join jobs using(job_id)

always yields 11 rows, as expected, and "solves" the issue.

I'm using SQLDeveloper and Oracle Database 11g. I'd appreciate any explanation as to how this is.

Thank you.

There are some Oracle natural join bugs. It's possible that you are hitting one of them. [mcve] [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/3404097) PS Your natural join vs using test doesn't tell us much because the left table of the natural join is the result of the left join & if it shares more columns with j than job_id then the two joins can return different results. Also if both d & e have job_id then the joins are ambiguous & should not be allowed. — philipxy, Nov 08 '18 at 01:13
[Bug 5031632 - Wrong results from NATURAL JOIN, Metalink Note: 5031632.8](https://community.oracle.com/thread/974712?tstart=0) — philipxy, Nov 08 '18 at 01:20
Possible duplicate [Oracle Natural Joins and Count(1)](https://stackoverflow.com/q/103389/3404097) — philipxy, Nov 08 '18 at 01:43

score 0 · Accepted Answer · answered May 29 '14 at 09:15

0

I'm supposing that jobs table is linked to employee's one.

Natural join is like an inner join, so your left join allow ur to show "No manager" result (and maybe other stuff) but with the natural/inner join you are tranforming the left join into inner join so this remove every not joined row of left join + employees without jobs.

You can use left join or natural left join on jobs instead.

answered May 29 '14 at 09:15

Ryx5

1,366
8
10

True, so why does count(*) indicate 513 rows? so does omitting job_id from the select list? – Searcherer May 29 '14 at 09:38
I'm a pro of `natural join` but it looks like the join is depending on the displayed fields so if you don't show any field of jobs nothing will be returned? In any case, as you can see natural join doesn't help you to **CONTROL** the output that's why on high development we don't use it. – Ryx5 May 29 '14 at 09:59
I thought as much, though wasn't sure as it didn't sound quite rhyme with the "structured approach" for executing those queries, where the from clause coming first and select clause last (or near last).., for this is implicitly suggesting the select clause is kind of evaluated before the from does its job.. , however. Thank you – Searcherer May 29 '14 at 10:13

score 0 · Answer 2 · answered May 29 '14 at 10:23

I would never use a natural join - far too error-prone as you have discovered (and now in a new way I hadn't previously been aware of!)

The issue here is that the natural join in your query is not between EMPLOYEES and JOBS, but between the result of

(select d.department_id "Department Nummber", department_name,
       nvl(last_name, 'NoManager!') "Manager",
       job_id
from departments d left outer join employees e on (d.manager_id=e.employee_id))

and JOBS. In this case there is a column to join on: JOB_ID.

But when you remove JOB_ID from the SELECT, you are now joining two "tables" that have no common columns (the first "table" being the select statement preceding the natural join). And the somewhat counter-intuitive (yet totally "logical") behaviour of natural join is that if there are no common columns then it performs a Cartesian product! See this trivial example:

SQL> select * from dept;

    DEPTNO
----------
        10
        20
        30

SQL> select * from jobs;

    JOB_ID
----------
      1000
      2000
      3000

SQL> select deptno, job_id from dept natural join jobs;

    DEPTNO     JOB_ID
---------- ----------
        10       1000
        10       2000
        10       3000
        20       1000
        20       2000
        20       3000
        30       1000
        30       2000
        30       3000

9 rows selected.

To get the behaviour you expected I think you'd need to use parentheses to get the appropriate joins - something like:

select d.department_id "Department Nummber", department_name,
       nvl(last_name, 'NoManager!') "Manager",
       job_id
from departments d left outer join 
(select * from employees e natural join jobs j) x
on d.manager_id=x.employee_id
order by 1;

Or better yet: never, ever use natural join in queries whose results matter!

I totally understand you, Tony, but then are you saying that the server evaluates the first join of this three way join, then, instead of continuing with the second join, skips to execute the select clause against that portion of the join, and then takes the resulting row set back to the from clause -again- and continue with the remaining "natural join"?! — Searcherer, May 29 '14 at 11:02
Yes, in effect. As the query is parsed, each subsequent join is applied to the preceding query as if that were in parentheses: `A`, `A join B`, `(A join B) join C`, `((A join B) join C) join D` etc. — Tony Andrews, May 29 '14 at 11:11
... which matters only when outer joins are introduced. `(A left join B) join C` is not the same thing as `A left join (B join C)`. When you don't specify parentheses like that, Oracle has to decide for you, just as it does in expressions like `a + b / c` or `a=1 and b=2 or c=3` — Tony Andrews, May 29 '14 at 11:15
@Searcherer Learn what LEFT JOIN returns: INNER JOIN rows plus unmatched left table rows extended by NULLs. (Always know what INNER JOIN you want as part of a LEFT JOIN.) We can freely move condition conjuncts without changing query meaning within sequences of non-OUTER JOINs & WHERE (as long as an alias has been introduced before being dotted) (including converting INNER JOIN ON to or from CROSS JOIN). But not from an OUTER JOIN ON to anywhere else because the ON determines not just what corresponding INNER JOIN ON rows are in the result but also what NULL-extended rows are added. — philipxy, Nov 07 '18 at 05:20
@Searcherer & TonyAndrews This answer wrongly says that the outer `select` is somehow involved in evaluating the natural join, but it is not. The outer select is done last. The left argument to the natural join is a temporary table `departments d left outer join employees e on d.manager_id=e.employee_id`. And if d & e have columns with the same name then that join has unique system-generated names for each. It is not true that execution "skips to execute the select clause". — philipxy, Nov 07 '18 at 06:22

Unexplained/strange behavior of a join query

2 Answers2