Solved

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

Posted on 2007-04-06
5
212 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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 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