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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top