Nested query or Inner join?

Jeremy Kadlec, Edgewood Solutions

Published: 14 Jun 2005

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

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please log in.

You have exceeded the maximum character limit.

Please provide a Corporate Email Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Related Q&A from Jeremy Kadlec

Performance differences between Windows and Linux-based databases

Get help making an apples-to-apples comparison of database performance in Windows vs. Linux systems. Performance and Tuning expert Jeremy Kadlec ... Continue Reading

Kappa Architecture pushes database to next level

Query Tuning for Developer, Beginner DBAs

Parallel execution: Determining SQL suitability, benefits

Parallel processing: Using parallel SQL effectively