Can we replace the where clause operator of '<>' with a join syntax condition.
Could someone explain me the dynamics behind the following query:
select a.b, c.d from a inner join c on a.b <> c.d
This could effectively replace the NOT IN and EXCEPT operators
No.. <> wont work as you expected. You query is similar to the following query,
select * from a cross join cWhere a.b <> c.d
Since it is set opertation your query will guide you to make a cross join and it only filter those values which are present on both the tables.
You have to use NOT IN or NOT EXISTS on your query. Join will be compared on both tables row by row. The IN & EXISTS will be verified with the one table value with other tables rows of value.
|||Since, I am avoiding the use of NOT IN & NOT EXISTS due to its obvious perofrmance degradations, I also wouldn't use a Cross Join (which I think for tables with more rows would be terribly slow). Hence, would I get a Join query to have a performance upgrade over them.
|||Hi there,
NOT IN and NOT EXISTS are no the same when it comes to performance. NOT EXISTS is much better in terms of performance especially if you're checking for a specific value as the query would return a TRUE value if a match is found and therefore avoid further iterations.
|||
I tend to use NOT EXISTS in these situations to take advantage of the semi-joins. Also, beware that the EXCEPT operator is typically slower.
Here are some previous threads that discuss NOT IN versus NOT EXISTS:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726903&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087482&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=637335&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654087&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1
Here is a thread that discusses NOT IN versus EXCEPT:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1021998&SiteID=1
