Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: What's wrong here? (NOT IN)

    Hello,
    seems this query is incorrect. What should do the trick?
    Code:
    SELECT ID1,ID2,Name
    FROM
    table1 
    WHERE (ID1,ID2) NOT IN
    (SELECT ID1,ID2 FROM table2)
    Thank you!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think the subquery for the IN clause can only return one column. You probably need to use EXISTS instead.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This would be correct syntax using the IN clause
    Code:
    WHERE ID1 NOT IN (SELECT ID1 FROM Table2)
    Add an AND/OR statement as needed.
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    I think AND/OR is not useable here, because it's more than just the combination of ID1 and ID2.
    What my query should do: If theres a combination of (ID1,ID2) in table1 but not in table2, I want ID1,ID2,Name from table1.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Give this a whirl.
    It concatenates ID1 and ID2, then compares it to the concatenation of ID1 and ID2 fro Table2.
    Not clean/pretty but it might work.
    Code:
    WHERE ID1 + ID2 NOT IN (SELECT ID1 + ID2 FROM Table2)
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Unless ID1 and ID2 are numeric...

    7207+1394 = 8601
    3233 + 5368 = 8601

    A little too much equality there, don't you think?

    I still say NOT EXISTS is the way to go.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ID1 + ' ' + ID2
    ..?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the answer to the original question ("seems this query is incorrect") is "no, it isn't"

    however, despite the fact that it's valid SQL (hint: what forum are we in), very few database systems support row constructors

    so perhaps silas could mention which database system the query should run in, and we can stop guessing at the syntax, as george seems to be doing by trying to perform arithmetic on two strings (that'll only work in one of those weird microsoft sql dialects, george)

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

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    Sorry, it's SQL Server 2005

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this should work in all databases --
    Code:
    select t1.ID1
         , t1.ID2
         , t1.Name
      from table1 as t1
    left outer
      join table2 as t2
        on t2.ID1 = t1.ID1
       and t2.ID2 = t1.ID2
     where t2.ID1 is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Your original query is absolutely fine in standardized SQL. It also works with DB2. So you have a system-specific restriction here.

    I wouldn't use the concatenation. First, that usually prevents index usage (concatenation must be done before the comparison). I would write this with a simple NOT EXISTS predicate:
    Code:
    SELECT ...
    FROM   table1 AS t1
    WHERE  NOT EXISTS ( SELECT 1
                        FROM table2 AS t2
                        WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id1 )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Mar 2007
    Posts
    97
    Thank you for your answers! Seems MS just don't support this. Do the last 2 queries have differences in performance?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could compare the EXPLAINs for starters...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, microsoft doesn't do EXPLAIN ... try SET SHOWPLAN ON, if i recall correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by silas
    Do the last 2 queries have differences in performance?
    The only way to tell is to actually measure it on your system. So you may want to ask that question in the SQL Server group - not in the group for general SQL questions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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