CODEWITHSUNDEEP
sqlt-sql
Jon Archway
Jon Archway

Reputation: 4892

SQL Query Help

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?

Upvotes: 1

Views: 279

Answers (4)

Jordan Parmer
Jordan Parmer

Reputation: 37174

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

Upvotes: 1

Oliver Michels
Oliver Michels

Reputation: 2917

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.

Upvotes: 1

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 4

cjk
cjk

Reputation: 46425

See How to do a Select in a Select

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

Upvotes: -1

Related Questions