SQL Server Performance

Join Doubt

Discussion in 'General Developer Questions' started by danny123, Sep 16, 2009.

  1. danny123 New Member

    I have 2 tables like this.
    CREATE TABLE tblTest3(custid INT,formid INT, dateadded DATETIME)
    CREATE TABLE tblTest4(custid2 INT,formid2 INT, dateadded2 DATETIME)
    INSERT INTO tblTest3 ( custid, formid, dateadded)
    VALUES ( 1,2,GETDATE() )
    INSERT INTO tblTest3 ( custid, formid, dateadded)
    VALUES ( 2,177,GETDATE() )
    INSERT INTO tblTest3 ( custid, formid, dateadded)
    VALUES ( 6,187,GETDATE() )
    INSERT INTO tblTest3 ( custid, formid, dateadded)
    VALUES ( 16,485,GETDATE() )


    INSERT INTO tblTest4 (custid2,formid2,dateadded2)
    VALUES ( 1,2,'2009-09-16 10:33:46.407' )

    INSERT INTO tblTest4 (custid2, formid2, dateadded2)
    VALUES ( 6, 187,'2009-09-16 10:34:44.537' )

    Now i want to query the table tblTest3 and get all the rows which does not have exactly same combination of CustId, FormId and Dateadded in tblTest4.
    so the query should give me a result with 2 records with custid 2 and 16 tblTest4 have exactly same combination of other 2 rows.
    Thanks!
  2. danny123 New Member

    I think i found the solution but do you guys think this will be efficient solution table which have the millions of rows?
    SELECT
    DISTINCT T1.*FROM
    tblTest3 T1 WHERE
    NOT EXISTS (SELECT * FROM tblTest4 T2 WHERE T1.custid = T2.custid2 AND t1.formid = T2.formid2 AND T1.dateadded = t2.dateadded2)
    Thanks again!
  3. Adriaan New Member

    It depends ...
    You might try replacing the NOT EXISTS subquery with a LEFT JOIN with IS NULL criteria, like this:
    SELECT DISTINCT T1.*
    FROM tblTest3 T1
    LEFT JOIN tblTest4 T2 ON T1.custid = T2.custid2 AND t1.formid = T2.formid2 AND T1.dateadded = t2.dateadded2
    WHERE T2.custid2 IS NULL
    This might give better performance if the number of unmatched rows is relatively small.
    Does tblTest4 have an index on (custid2, formid2, dateadded2)?
    And is it unique?
  4. raviramemani New Member

    The following query would give you the answer , but let me know if this is what you expected
    Query :Select * from tbltest3 where custid not in (Select custid2 from tbltest4 )
    Logic : As you need exactly matched records ,even if a single column does not match it will meet the requirement.
  5. danny123 New Member

    Thanks Adriaan, This is definitly better and more efficient solution!
    Yes i have index on those 3 fields.
  6. atulmar New Member

    Wrong..
    Now your problem statement is changed?
    You said you want to avoid same combination of column values, not only one column value.

Share This Page