>>>Hello!
>>>I was just wondering if i can write a query that selects all the data not found in another table. like
>>>select *
>>>from table
>>>where 100 is in one table... but not in the other table
>>>?
>>
>>Try this syntax
>>
>>SELECT T1.* from myTable T1 LEFT JOIN myOtherTable T2 on T1.ID = T2.ID where T2.ID IS NULL
>>
>>This syntax is more robust than the other (in case you have NULLs in T2.ID) it would not select anything if using NOT IN syntax.
>>
>>In addition, it's more likely to be more performant.
>>
>>See also this link
>>
>>
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#4>
>Ah you are right partially. Nulls are evils. A join would too erroneously select rows with null values on left:)
>
>Performance wise I disagree join perfroms better. Last time I tested them I remember join was the slowest of all (this year).
>Cetin
It depends, but in most cases I tried LEFT JOIN was the fastest method.
If it's not broken, fix it until it is.
My Blog