Re:Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement

I have Table A and Table B.
I want to select those rows in Table A, excluding the one available in Table B with respect to say CusID
Can anyone help me with a select Sql for this.

what is the structure of the two tables? column names and datatypes ?

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

I Just give a english sentence which need to be converted as a valid Sql select statement :

Select A.Name, A.Address from Table1 A, Table2 B where A.CusID not in Table2 B [ Note: Table2 has the reference CusID ]

a few different ways...

option 1 is to left join and then only select where table b is empty

Code:

---

SELECT *
from TableA A
left join TableB B on A.CustID = B.CustID
where B.ID is null

---

option 2 is to use the except clause
http://technet.microsoft.com/en-us/l.../ms188055.aspx

Code:

---

select CustID
from TableA
EXCEPT
select CustID
from TableB

---

option 3 is to use a Not In clause

Code:

---

select *
from TableA A
where CustId Not In (Select CustID from TableB)

---

personally I go with option 1... I find it generally performs better than option 3
Option 2 is good, but it only gives the IDs, which would then need to be joined back to the table to get the full data.


-tg

Thanks Techgnome. Now My Select Statement is like the below - and I'm accessing the MS Access DB

Code:

---

vSql2 = "Select Count(CusID) as Cnt2  from Contacts where StaffID = " & gSTaffID & " and  " _
& " CusID NOT IN (Select CusID from DISCON where StaffID = " & gSTaffID & ") and  " _
& " CusID NOT IN (Select CusID from RECMAST where StaffID = " & gSTaffID & ")"

---

This SQL process very slowly. Each select statement in it has some more conditions in where clause which i did't show here. What changes i can make, so that it process faster?

NOT EXISTS Clause seem to be a better solution which gives out the same result in a FASTER way. Here is my code...

Code:

---

vSql2 = "SELECT Count(A.CusID) as Cnt2  FROM Contacts A WHERE A.StaffID = " & gSTAFFID & " and  " _
& " NOT EXISTS (Select NULL from DISCON B where B.CusID = A.CusID and B.StaffID = " & gSTAFFID & ") and  " _
& " NOT EXISTS (Select NULL from RECMAST C where C.CusID = A.CusID and C.StaffID = " & gSTAFFID & ")"

---

I'm still awaiting to learn how to accomplish the same with "LEFT JOIN - IS NULL" Clause.
At the same time can anyone explain me technically how this "NOT EXISTS" method is faster than the "NOT IN" method .... ?

A useful documentation on this subject ..... I just found

http://explainextended.com/2010/05/2...lable-columns/

A modification (green coloured in the below code) in my NOT IN clause Sql, makes this too super-faster !!!!.
Earlier I did't do this. Sorry for my ignorance ......

Code:

---

vSql2 = "SELECT Count(A.CusID) as Cnt2  FROM Contacts A WHERE A.StaffID = " & gSTAFFID & " and  " _
& " A.CusID NOT IN (Select B.CusID from Discon B where B.CusID = A.CusID and B.StaffID = " & gSTAFFID & ") and  " _
& " A.CusID NOT IN (Select C.CusID from RecMast C where C.CusID = A.CusID and C.StaffID = " & gSTAFFID & ")"

---

Actually NOT IN clause is the slowest because it doesn't use indexes. Left join will not use indexes ONLY if the records don't match which should be faster because you are matching a "subset" of records. Also IN/NOT IN has a limit of items. Although Microsoft's explanation is fuzzy at best, we've had problems running more than 2000 number of items in the IN clause.

I can also say from my experience that IN and NOT IN are poor performers with lots of rows.

If you want to see for sure how these thing stack up just look at the ACTUAL EXECUTION PLAN in SSMS.

I've used that many times in my career to figure out slow queries. I just took a STORED PROCEDURE that was taking 10 seconds to do three SELECT's and made it run "instantly" by just looking at the ACTUAL EXECUTION PLAN (although figuring out another method to accomplish the same task did take some time!)

Serge ! I'm having problem in writing an equivalent query (to the one in my Post #9 using NOT IN) with LEFT JOIN-IS NULL. Can you help me please ?

Be slightly careful with the Left Join approach, it's not the logical equivalent of an EXISTS or an IN statement. A left join will return multiple rows for each record in Table A if there is more than one matching record in table B whereas an EXISTS or IN will return only a single row. That doesn't matter if you're checking for records that DON'T exist in tale B (because these will only ever return a single row) but sooner or later you'll use it to check for records that DO exist in table B and get a wildly different result from the one you expect.

I also tend to avoid an IN because a. it tends to be slower and b. it doesn't work on more than one join condition, e.g. when you have a compound primary key.

So that's always left the EXISTS statement as my favoured approach because it's explicit (you're checking whether something exists so the EXISTS keyword seems pretty clear) and it generally performs well as long as the rest of the query is well formed and the joining columns are indexed. Also, as you've discovered in post 9, you can limit the contents of the inner query to really optimise it. The one downside is if you're having to join to the same sub-query several times in which case the engine won't spot that it's actually the same thing so will resolve it separately each time. In that circumstance just break it out to a CTE.

With all that said, although I'm vaguely aware of the Intersect and Except statements that TG linked to they haven't really managed to force their way into the front of my brain yet. The more I think about it the more I think they're probably the right construct to use in cases like this. I haven't done any experimenting to check performance though.

Of course, all that's just rules of thumb and while the various constructs have predictable logical results, their results in terms of performance can differ. So, as Szlamany said, check the execution plan and be prepared to break your own rules if they're not right for a particular query.

BTW The Left join query you're asking for would be

Code:

---

SELECT Count(C.CusID) as Cnt2 
FROM Contacts C
Left Join Discon D
  On C.CusID = D.CusID
  And D.StaffID = @StaffID
Left Join RecMast R
  On C.CusID = R.CusID
  And R.StaffID = @StaffID
Where C.StaffID = @StaffID
And D.CusID is null
and R.CusID is null

---

I've typed that straight off the cuff without syntax checking it so beware of typos but I think I got it right.

I'd also question the logic a bit. It looks like you're trying to get all contacts for a member of staff who haven't been disconnected. What you're actually getting is all the contacts for a member of staff who haven't been disconnected by the same member of staff... or something like that. If you don't want to limit it to the same member of staff doing the disconnect then simply drop the Staff ID clauses from the joins (but not the where clause). If I've missunderstood the query entirely then just disregard this.