Q

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

  1. Using Type I nested query:
    SELECT Cust_id, Cust_name
     FROM customer WHERE cust_id IN
    (SELECT cust_id FROM Order)
    

  2. 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.
This was first published in June 2005

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close