SQL Query Help

Question

Duplicate:

How to choose Select aa Select

I have 2 tables:

TABLE1 Table1Id TABLE2 Table2Id Table1Id UserId 

TABLE2 contains thousands of records. I want to return a list of TABLE1 entries, where there is no entry in TABLE2 for it for a specific user. So, if there is no foreign key entry in table 2. Type request:

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

However, this query is very slow. What will be the most effective way to get the desired results?

Answer 1

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 also check the indices as ck suggested

Answer 2

What about

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

I am not sure that MsSql supports minus. If not, you should try a correlated subquery.

Answer 3

You can also use the "EXCEPT / MINUS" intersection to get only the differences between the two tables if 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 

Answer 4

See How to choose Select aa Select

Also, make sure that all the fields you request have a suitable index.