Q

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close