Keep It Simple, Stupid | |
PerlMonks |
Re: SQL JOINs vs WHERE statementsby EdwardG (Vicar) |
on Aug 19, 2003 at 17:31 UTC ( [id://284962]=note: print w/replies, xml ) | Need Help?? |
You're not crazy, you can indeed get an equivalent join using a WHERE clause. But I don't recommend it. It mixes up your different types of clauses (JOIN vs WHERE), which makes your SQL harder to understand, and worse, you can get unintuitive results. For example, what would you expect the following to return? Would you be surprised to get the following results?
(Hint: You should be surprised, the SELECT was trying to exclude NULLS) The source of this ambiguity is that the IS NOT NULL part of the WHERE clause got evaluated before the join part, and the join part then re-introduced the NULLs. In contrast, the LEFT OUTER JOIN makes the intention clearer (albeit somewhat self-defeating in this example)
Results: Update: It seems that Oracle gives different results to MS SQL Server 2000, where this code was tested.
In Section
Seekers of Perl Wisdom
|
|