When does outer join become inner?

Database
Enthusiast

When does outer join become inner?

can anyone please explain the scenarios where an outer join becomes an inner join..
3 REPLIES
WAQ
Enthusiast

Re: When does outer join become inner?

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)
Teradata Employee

Re: When does outer join become inner?

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;

Enthusiast

Re: When does outer join become inner?

Thanks Fred, that was helpful!