Non-equi Joins using <>

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

Non-Equi Joins

I am taking Microsoft Course 4329 on Joining Data from Multiple Tables in
SQL Server 2005, and I am stumped in the Lab, Task 2, Step 3.

How can I rewrite the following SQL Statement to produce the same results by
using a non-equality operator in the join?

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e join person.contact c on e.contactid=c.contactid
where gender='f' and vacationhours>50
order by hours desc

Thanks in advance for your help!

--Justin--

There may be mulitple answers,

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e CROSS JOIN person.contact c where e.contactid=c.contactid
and gender='f' and vacationhours>50
order by hours desc

or

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e JOIN person.contact c On vacationhours>50 where e.contactid=c.contactid
and gender='f'
order by hours desc