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.
This was first published in September 2003