Problem solve
Get help with specific problems with your technologies, process and projects.
Nested query or Inner join?
I am trying to find out which is more efficient -- a Type I nested query or an Inner Join -- when data from one table (thousands of records) is to be listed using a criterion based on data from a second table.
Example: List all customers who have an order
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
Using Type I nested query: SELECT Cust_id, Cust_name FROM customer WHERE cust_id IN (SELECT cust_id FROM Order)
Using Inner Join: SELECT Cust_id, Cust_name FROM customer,order WHERE customer.cust_id = order.cust_id
Any information you can provide would be appreciated. Thanks.
Under these circumstances, I do not have a definitive answer based on the volume of data, indexes and SQL Server configurations. I would recommend taking a look at the
graphical query plan from Query Analyzer. If I had to provide a guess, I would say Option 2, but based on the results from the query plan you will have the answer. I would also recommend taking a look at the
ANSI JOIN syntax. Be on the lookout for a tip on query plans in the near future.
Do you have comments on this Ask the Expert Q&A? Let us know.
Do you have comments on this Ask the Expert Q&A? Let us know.
