All Forums Database
Suma.Manu 15 posts Joined 11/09
22 Apr 2010
When does outer join become inner?

can anyone please explain the scenarios where an outer join becomes an inner join..

WAQ 158 posts Joined 02/10
23 Apr 2010

Which outer join are you talking about?
> LEFT: left outer acts like inner join when all the match from left table is found in the right table
> RIGHT: vice versa of LEFT
> FULL: full outer join acts like inner join when both tables have same number of rows and all the rows matched (based on ON condition)

Fred 1096 posts Joined 08/04
26 Apr 2010

Perhaps you are referring to the case where the optimizer determines that the filter (WHERE) criteria will eliminate all rows in the result that would have had NULLs due to no match in the inner table, so an OUTER JOIN is unnecessary.

For example, this would become an INNER JOIN:
SELECT a.c1, b.c2
FROM a LEFT OUTER JOIN b ON a.c3 = b.c3
WHERE b.c2 > 0;

Suma.Manu 15 posts Joined 11/09
26 Apr 2010

Thanks Fred, that was helpful!

You must sign in to leave a comment.