What's wrong here? (NOT IN)

silas · #1 (**permalink**) 03-29-07, 11:23

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!

RedNeckGeek · #2 (**permalink**) 03-29-07, 11:32

I think the subquery for the IN clause can only return one column. You probably need to use EXISTS instead.

gvee · #3 (**permalink**) 03-29-07, 11:36

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.

silas · #4 (**permalink**) 03-29-07, 12:02

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.

gvee · #5 (**permalink**) 03-29-07, 13:07

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)

RedNeckGeek · #6 (**permalink**) 03-29-07, 13:19

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.

gvee · #7 (**permalink**) 03-29-07, 19:10

ID1 + ' ' + ID2
..?

r937 · #8 (**permalink**) 03-29-07, 21:46

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)

silas · #9 (**permalink**) 03-30-07, 11:41

Sorry, it's SQL Server 2005

r937 · #10 (**permalink**) 03-30-07, 14:52

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

stolze · #11 (**permalink**) 03-31-07, 12:10

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 )

silas · #12 (**permalink**) 03-31-07, 14:48

Thank you for your answers! Seems MS just don't support this. Do the last 2 queries have differences in performance?

r937 · #13 (**permalink**) 03-31-07, 14:51

you could compare the EXPLAINs for starters...

r937 · #14 (**permalink**) 03-31-07, 14:54

oops, microsoft doesn't do EXPLAIN ... try SET SHOWPLAN ON, if i recall correctly

stolze · #15 (**permalink**) 03-31-07, 18:44

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech