CODEWITHSUNDEEP
sqlsql-server-2008
Sombir Kumar
Sombir Kumar

Reputation: 1891

In which case "Left OUTER JOIN" is better over "NOT IN" query and In which case "NOT IN" or "NOT EXIST" or "IN"?

Hi can anyone explain me where I should use Outer Join and where I should use NOT IN or NOT EXIST query.

Which one is better in which case ?

Upvotes: 0

Views: 115

Answers (3)

HLGEM
HLGEM

Reputation: 96600

Generally not exists is faster in SQL server than the others for the equivalent query. However, with a left join you may not be doing an equivalent query. Left joins are also used when you want some of the data from both tables. For instance you have a User table and an Address table, but not all users have addresses. So you use a left join to get the addresses that you do have and a null value for those you do not. You can't do this with not exists or not in so left join is your only choice.

Upvotes: 0

qxg
qxg

Reputation: 7036

Generally speaking, if EXISTS or NOT EXISTS is applicable, use it. They are semi-join. It returns result immediately when the first match or not match row is found, and it throw away what just found.

It makes no sense to say which is better just by syntax. SQL Server data engine optimizer will decide the actual execution plan. Quite different query might generate the exact same execution plan. If you're not sure which is better, check you actual execution plan.

Upvotes: 1

tarzanbappa
tarzanbappa

Reputation: 4968

We can use joins when we need to get data from different tables by connecting them.

And We can use IN, NOT IN or NOT exists in our where clause to filter our data according to our requirement.

Refer these links for more info..

http://www.w3schools.com/sql/sql_join.asp

http://www.w3schools.com/sql/sql_in.asp

NOT IN vs NOT EXISTS

Upvotes: 0

Related Questions