Solved

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

Posted on 2007-04-06
5
198 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now