Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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.

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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)

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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 ]

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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 .... ?

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Sql Select Statement

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

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

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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.

  10. #10
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    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.

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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!)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    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 ?

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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
  •  



Click Here to Expand Forum to Full Width