Posted by Erland Sommarskog on 06/25/05 01:08

blueghost73@yahoo.com (blueghost73@yahoo.com) writes:
> I'm no expert, so this probably isn't the most efficient way to do
> this, but I think this will work:
>
> select A.* from A, B
> where A.a1 *= B.b1
> and A.a2 *= B.b2
> and A.a4 *= B.b4
> and B.b1 is null

*= is a older form of outer join which has all sorts of funny
quirkes with it. I am not going to find why this does not work.

Use the new ANSI syntax instead:

select A.*
from A
left join B ON A.a1 = B.b1
and A.a2 = B.b2
and A.a4 = B.b4
where and B.b1 is null

But I much prefer NOT EXISTS for this type of query, as it much better
expresses what you are looking for.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]