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.
- INNER JOIN
select p.id, p.name from products p inner join inventory i on p.id = i.id
- WHERE EXISTS (correlated subselect)
select id, name from products p where exists ( select 1 from inventory where id = p.id )
- 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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.