• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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?
0
chuang4630
Asked:
chuang4630
  • 2
  • 2
1 Solution
 
dreadyCommented:
select id from CompleteList where id not in (select distinct ItemID from UsedItems)

should do the job. Don't think a join will make it more efficient.
0
 
NightmanCTOCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or, use a LEFT JOIN technique:

Select  c.*
FROM  CompleteList c
LEFT JOIN UsedItems u
  ON u.ItemID=c.ItemID
WHERE u.ItemID IS NULL
0
 
dreadyCommented:
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
0
 
NightmanCTOCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now