Bug in Outer Join Logic, Oracle 9204?
why do the following queries return different results:
Code:
tableL
LEFT OUTER JOIN
tableR
ON
tableL.primarykey = tableR.primarykey AND
tableR.colour = 'orange'
Code:
tableL
LEFT OUTER JOIN
tableR
ON
tableL.primarykey = tableR.primarykey AND
'orange' = tableR.colour
the order of the truths in the ON clause matters! can anyone explain why?
try for yourself:
Code:
select *
from
(
select '1' as pk, 'square' as lef from dual union all
select '2' as pk, 'circle' as lef from dual union all
select '3' as pk, 'rectangle' as lef from dual union all
select '4' as pk, 'trangle' as lef from dual
) tableL
LEFT OUTER JOIN
(
select '3' as pk, 'red' as rig from dual union all
select '4' as pk, 'blue' as rig from dual union all
select '5' as pk, 'red' as rig from dual union all
select '6' as pk, 'blue' as rig from dual
) tableR
ON
tableL.pk = tableR.pk AND
'red' = tableR.rig
Code:
select *
from
(
select '1' as pk, 'square' as lef from dual union all
select '2' as pk, 'circle' as lef from dual union all
select '3' as pk, 'rectangle' as lef from dual union all
select '4' as pk, 'trangle' as lef from dual
) tableL
LEFT OUTER JOIN
(
select '3' as pk, 'red' as rig from dual union all
select '4' as pk, 'blue' as rig from dual union all
select '5' as pk, 'red' as rig from dual union all
select '6' as pk, 'blue' as rig from dual
) tableR
ON
tableL.pk = tableR.pk AND
tableR.rig = 'red'
results for me with first query:
Code:
PK LEF PK RIG
1 square
2 circle
3 rectangle 3 red
4 trangle 4 blue
huhh???
and second
]
Code:
PK LEF PK RIG
1 square
2 circle
3 rectangle 3 red
4 trangle
thats correct...
huh???
