Solved

Question on JOIN to query all items NOT in "INNER JOIN" ?

Posted on 2007-04-06
5
203 Views
Last Modified: 2010-03-19
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
Comment
Question by:chuang4630
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18864261
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18864913
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18864937
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
 
LVL 11

Expert Comment

by:dready
ID: 18866178
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18866350
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question