SQL Query Help

Question

Duplicate:

How to do a Select in a Select

I have 2 tables:

TABLE1
Table1Id

TABLE2
Table2Id
Table1Id
UserId

TABLE2 has thousands of entries in it. I want to return a list of TABLE1 entries where there isn't an entry in TABLE2 for it for a particular user. So, where there isn't a foreign key entry in TABLE2. A query like:

select count(*) from TABLE1 where Table1Id not in (
select Table1Id from TABLE2 where id_user = 1)

However, that query runs very slowly. What would be the most efficient way of getting the results I require?

Answer 1

See http://stackoverflow.com/questions/760950/how-to-do-a-select-in-a-select

Also, make sure that any fields you are querying have a suitable index.

Answer 2

There is a similar question

I think it would be better

SELECT COUNT(*) 
FROM TABLE1 
WHERE NOT EXISTS (SELECT Table1Id FROM TABLE2 WHERE TABLE2.Table1Id = TABLE1.Table1Id AND UserID = 1)

I would check the indexes also, as ck suggested

Answer 3

Same as here: http://stackoverflow.com/questions/760950/how-to-do-a-select-in-a-select

Answer 4

What about

select Table1Id from TABLE1 
minus
select Table1Id from TABLE2 where id_user = 1

I am not sure, it MsSql support minus. If not, you should try a correlated subquery.

Answer 5

You can also use the 'EXCEPT/MINUS' intersect to get only differences between the two tables as long as the selection returns the same field types/order.

SELECT TABLE1ID
FROM TABLE1
EXCEPT -- or MINUS in Oracle
SELECT TABLE1ID
FROM TABLE2
WHERE USER_ID = 1