Three ways to query a table relationship

Three ways to query a table relationship

I have a normalized database featuring a table with manufacturer names, a table with all the different manufacturer products, and finally an inventory table with actual records of existing products that draw from the other tables. I have a listbox that I want to return a list of products from the products table, but only where the products exist in the actual inventory table. I only want the user to have option that will actually return something rather than say "Sorry that isn't available at this time". What would my SQL statement be to accomplish this?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

You want to return rows from one table, qualified by a condition that the value of some column on each row must exist in another table. There are at least three ways to do this, and they are logically equivalent.

  1. INNER JOIN 
    select p.id, p.name
  from products p
inner 
  join inventory i
    on p.id = i.id
  2. WHERE EXISTS (correlated subselect) 
    select id, name
  from products p
 where exists 
       ( select 1
           from inventory 
          where id = p.id )
  3. WHERE ... IN (subselect) 
    select id, name
  from products  
 where id in
       ( select id
           from inventory )

Your product-inventory relationship is probably one-to-zero-or-one. That is, a product may exist in inventory, or it may not, but if it does, only once.

In a one-to-zero-or-many relationship, the three methods will be equivalent only if you add the keyword DISTINCT to the first method, the inner join.

Which of these methods you choose is up to you. Do they differ in performance? Usually, no. The database optimizer typically executes all three of them the same way, as a join. In practice, there may be subtle differences, depending on whether indexes are declared on the id columns, and of course depending on which database you happen to be using.

For best results, consult your DBA (database administrator), and ask to have an EXPLAIN analysis done on each method.

For More Information


This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top