NULL problem in SQL Server Options

[DeaPeaJay]

I've brainstormed and researched this problem but I just can't seem to figure it out.

This is my SQL

CODE

select name, title, department, about, notes from Providers
where name != ANY
    (select provider from needProviders
        where needID = 1)

pretty straightforward. the provider column is a foreign key to the name column. I'm trying to remove from the result set the names that are in the inner query. It works great! Just as I expect it would, unless the inner query returns an empty result set. Then, it doesn't return anything at all, when it *should* return everything, since name is not equal to an empty result set.

I'm just confused as to what SQL Server is doing, I suspect that I wouldn't have this problem with mySQL. Or am I missing something obvious?

[DeaPeaJay]

Ok, that's just weird. I used "not in" instead of "!= ANY" and now it works. Logically I don't see why that would make a difference but it did.

This post has been edited by DeaPeaJay: Feb 14 2007, 12:55 PM