CODEWITHSUNDEEP
sqloracle-databaseouter-join
Youssef Khloufi
Youssef Khloufi

Reputation: 705

Move joins from Where clause to From clause in an Oracle query

I have the follwing Oracle query and I want to move the joins from the Where clause to the From clause

select 
    * 
from
    A, B, C
where
    A.a (+)= B.a
    AND A.b (+) = B.b
    AND A.b = C.b;

So I came up with the following query

select
    *
from
    C,
    B left join A on (B.a = A.a and B.b = A.b and C.b = A.b);

I want to know if the two queries are semantically the same and if both will output the same results

*** Edit: Attempt 2

select
    *
from    
    B left join A on (B.a = A.a and B.b = A.b)
    inner join C on C.b = A.b;

Upvotes: 0

Views: 283

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

In the old Oracle join syntax the (+) is to be applied to all columns of the outer-joined table. In your original query it is hence table A that gets outer-joined to table B, because

FROM a, b
WHERE a.a (+) = b.a AND a.b (+) = b.b

is

FROM b
LEFT join a ON a.a = b.a AND a.b = b.b

But AND a.b = c.b in your query renders this a mere inner join, because it would have to be AND a.b (+) = c.b for a working outer join (in which case you would probably want a.b = c.b, too, in order not to cross join the two tables).

So your query boils down to:

select * 
from a
inner join b on b.a = a.a and b.b = a.b
inner join c on c.b = a.b;

Upvotes: 2

Popeye
Popeye

Reputation: 35910

If the following is your working query then I must say it is not doing what is expected.

select 
    * 
from
    A, B, C
where
    A.a (+)= B.a -- b left join a
    AND A.b (+) = B.b -- b left join a
    AND A.b = C.b; -- c inner join a

It is nothing but the inner join of all three tables as the last condition made it inner join between a and c. it will return data only if there is data available both in a and c (and offcourse b). (inner join)

To get the exact result, you simply need an inner join.

SELECT * FROM
B INNER JOIN A ON A.A=B.A AND A.B = B.B
INNER JOIN C ON A.B = C.B

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You probably intend this query as:

select * 
from B left join
     A
     using (a, b) left join
     C
     using (b)

Upvotes: 0

Related Questions