|
|
Posted by Roy Harvey on 06/22/07 11:59
The entire idea of using LEFT OUTER JOIN with a NULL test to give the
same results as NOT EXISTS is based on the fact that the WHERE clause
executes after the JOIN processing.
JOINs occurs before anything else. The WHERE clause acts on the
result of the JOIN. If you changed the WHERE to an AND in the example
shown, the test B.ColA IS NULL would move into the ON clause and
become part of the JOIN. Inside the JOIN that column will never be
NULL.
Personally I much prefer the NOT EXISTS syntax over this approach. I
think it shows what you are trying to retrieve - rows in one table
without matches in the other table - more clearly.
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 04:48:02 -0700, rshivaraman@gmail.com wrote:
> SELECT A.ColA, A.ColB
> FROM RS_A A
> LEFT OUTER JOIN RS_B B
> ON A.ColA = B.ColA
> AND B.ColB = B.ColB
> WHERE B.ColA IS NULL
>
>Hi Harvey :
>
>I was implementing this solution at another place and i mentioned an
>AND instead of a WHERE. and it did not work.
>So i used WHERE and viola, things worked.
>But actually how can i use a WHERE Clause?
>I thought WHERE Clauses were for the FROM table and not for the tables
>that are joined to the FROM table
>
>Is my assumption correct ? Obviously it is wrong,
>So is it a normal thing to mention conditions where only one table is
>needed(like in the above example)
>in the WHERE Clause, as then i can do that will all my other queries
>where i am joining, and i always have mentioned unique conditions
>pertaining to that table alone, using an AND clause.
>
>thank you in advance
>RS
>
>
Navigation:
[Reply to this message]
|