Problem solve Get help with specific problems with your technologies, process and projects.

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.

    select p.id, p.name
      from products p
      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

Dig Deeper on Oracle and SQL