-
Oct 9th, 2013, 09:40 AM
#1
Thread Starter
Addicted Member
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.
Last edited by raghavendran; Oct 10th, 2013 at 05:56 AM.
-
Oct 9th, 2013, 09:57 AM
#2
Re: Sql Select Statement
what is the structure of the two tables? column names and datatypes ?
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Oct 9th, 2013, 10:27 AM
#3
Re: Sql Select Statement
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)
-
Oct 9th, 2013, 10:46 AM
#4
Thread Starter
Addicted Member
Re: Sql Select Statement
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 ]
-
Oct 9th, 2013, 10:50 AM
#5
Re: Sql Select Statement
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
-
Oct 10th, 2013, 12:37 AM
#6
Thread Starter
Addicted Member
Re: Sql Select Statement
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?
Last edited by raghavendran; Oct 10th, 2013 at 05:48 AM.
-
Oct 10th, 2013, 03:55 AM
#7
Thread Starter
Addicted Member
Re: Sql Select Statement
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 .... ?
-
Oct 10th, 2013, 04:09 AM
#8
Thread Starter
Addicted Member
Re: Sql Select Statement
A useful documentation on this subject ..... I just found
http://explainextended.com/2010/05/2...lable-columns/
-
Oct 10th, 2013, 05:42 AM
#9
Thread Starter
Addicted Member
Re:Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement
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 & ")"
Last edited by raghavendran; Oct 10th, 2013 at 05:52 AM.
-
Oct 11th, 2013, 01:48 PM
#10
Re: Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement
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.
Last edited by Serge; Oct 11th, 2013 at 01:52 PM.
-
Oct 11th, 2013, 03:40 PM
#11
Re: Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement
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!)
-
Oct 12th, 2013, 03:04 AM
#12
Thread Starter
Addicted Member
Re: Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement
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 ?
-
Oct 12th, 2013, 03:42 AM
#13
Re: Using NOT-IN, NOT EXISTS, LEFT JOIN-IS NULL clauses in Sql Select Statement
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|