?
Solved

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

Posted on 2007-04-06
5
Medium Priority
?
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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