Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Lightbulb Unanswered: Seeking advise on the diff

    Greetings,

    I need your expertise on my issue. I do two tables, each has only one simple column (varchar(500) type). Also, each table has roughly about 4 million rows. What is the best way to write a query that show rows in tableA are not in tableB?

    Your guidance or sample query is truly appreciated.

    john9569

    MSSQL server 2008

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT col FROM table1
    EXCEPT
    SELECT col FROM table2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    where not exists

    ..we can also provide anothr condition "where not exists"

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    ..or left outer join where right side is null
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gentlemen, please

    the question was "What is the best way to write a query that..."

    the obvious answer is the EXCEPT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2011
    Posts
    3
    Thank you, all

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    the question was "What is the best way to write a query that..."
    Since no criteria were provided for determining the quality of a solution, that means that every solution offered is implicitly the best (at least to the person that offered that solution). R937 thinks that his solution is the best, as does everyone else that offered a solution (each author sees their solution as the best).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    Since no criteria were provided for determining the quality of a solution...
    on the contrary, there was one criterion... the best way to write

    both the WHERE NOT EXISTS (correlated subquery) and LEFT JOIN WHERE joincolumn IS NULL solutions are substantially more verbose and difficult to write

    so by the only criterion provided by the original poster, i still say the EXCEPT solution is the best

    so there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I want to test all 3 for performance. I want to believe that Rudy's is the best because it is the newest syntax. I am on a deployment call and the other team is killing me with their deployment night drama, so I might be be back with numbers in a bit.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Memberid is a varchar field that represents the number on your health insurance card. MyTable is a fake table name.

    The answer seems to be that that the SQL Server does not care, so Rudy wins on style.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I seem unable to post the code for some reason. Here are the results...

    TEST LEFT OUTER JOIN

    (3284489 row(s) affected)
    Table '#table2__________________________________________ __________________________________________________ _________________000000000214'. Scan count 5, logical reads 11271, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#table1__________________________________________ __________________________________________________ _________________000000000213'. Scan count 5, logical reads 11271, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    TEST CORRELATED SUBQUERY

    (3284489 row(s) affected)
    Table '#table1__________________________________________ __________________________________________________ _________________000000000213'. Scan count 1, logical reads 11272, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#table2__________________________________________ __________________________________________________ _________________000000000214'. Scan count 1, logical reads 11272, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    TEST EXCEPT

    (3284489 row(s) affected)
    Table '#table2__________________________________________ __________________________________________________ _________________000000000214'. Scan count 1, logical reads 11272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#table1__________________________________________ __________________________________________________ _________________000000000213'. Scan count 1, logical reads 11272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Sep 2010
    Posts
    153

    Smile except is not compatible with all sql servers.

    I know the above person has mentioned sql 2008 but just to make sure that the query works in all sql servers i suggested something else. if you are using 2005 or 2008 then it;s fine. I agree with R397.


    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •