chuang4630
asked on
Question on JOIN to query all items NOT in "INNER JOIN" ?
I need to work on a query to get records that are not found in another table. For example, table CompleteList saves all the list items. Table UsedItems saves items being used. I need to create a query to get all the available items: CompleteList minus UsedItems. I think I could use subquery to do it, but it low efficient. How do we use join to achieve this?
Select
*
FROM
CompleteList c
WHERE
NOT EXISTS (SELECT * FROM UsedItems u WHERE u.ItemID=c.ItemID)
Usually, NOT EXISTS is more efficient - the distinct statement will result in a hash merge / hash join internally, as SQL actually has to sort the data to eliminate duplicates.
*
FROM
CompleteList c
WHERE
NOT EXISTS (SELECT * FROM UsedItems u WHERE u.ItemID=c.ItemID)
Usually, NOT EXISTS is more efficient - the distinct statement will result in a hash merge / hash join internally, as SQL actually has to sort the data to eliminate duplicates.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nightman: This is interesting, hope you can elaborate a bit.
If i would leave out the distinct, so use this statement:
select id from CompleteList where id not in (select ItemID from UsedItems)
Is NOT EXISTS still more efficient, and what about angellls technique?? I always thought that the query optimiser would be smart enough to end up with the same execution paths...
(just curiuos, might learn something (-:)
dready
If i would leave out the distinct, so use this statement:
select id from CompleteList where id not in (select ItemID from UsedItems)
Is NOT EXISTS still more efficient, and what about angellls technique?? I always thought that the query optimiser would be smart enough to end up with the same execution paths...
(just curiuos, might learn something (-:)
dready
angelIII's techinque is also efficient. The reality is that you would need to test each one to see which is more efficient - this could vary depending on your indexing strategy, and I have sometimes seen NOT EXISTS perform worse than the left join.
NOT EXISTS should still be faster than an IN clause.
The query optimisers job is *not* to find the best plan. It's job is to find a good plan, fast. If you soley rely on it, your queries will always underperform. You need to look at the execution plan and query cost yourself, and compare them.
NOT EXISTS should still be faster than an IN clause.
The query optimisers job is *not* to find the best plan. It's job is to find a good plan, fast. If you soley rely on it, your queries will always underperform. You need to look at the execution plan and query cost yourself, and compare them.
should do the job. Don't think a join will make it more efficient.