T-SQL: Performance when using an inner query on a join

Question

Here's the situation I'm dealing with. The original query I had was:

SELECT c.CustID, a.City, a.Country FROM Customers AS c
    LEFT OUTER JOIN Addresses AS a ON c.CustID = a.CustID
    WHERE c.LastName = 'Jones'

So I'd like to show all customers with last name Jones, even ones without any address entries, and show associated addresses for them. But what if I want a WHERE clause on the addresses but still show all customers? For example if I do this:

SELECT c.CustID, a.City, a.Country FROM Customers AS c
    LEFT OUTER JOIN Addresses AS a ON c.CustID = a.CustID
    WHERE c.LastName = 'Jones' AND a.Country = 'United States'

I lose any customers that are not in the United States. But that's not what I want. I want all customers with last name 'Jones', and only omit addresses that are not in the United States. This is the solution I came up with:

SELECT  c.CustID, a.City, a.Country FROM Customers AS c
    LEFT OUTER JOIN
        (SELECT City, Country FROM Addresses
        WHERE Country = 'United States') AS a
    ON c.CustID = a.CustID
    WHERE c.LastName = 'Jones'

In this case, I still get all customers with the last name Jones, but don't see addresses that are outside the US, which is what I wanted.

Here's my issue: In the third query, I'm assuming that SQL Server fetches all US addresses and then does the join with the Customers table, meaning many non-Jones addresses were fetched unnecessarily. In the second query, I'm wondering if SQL Server only fetches US addresses where LastName = 'Jones' in the first place, which I would think would make the query far faster. So is there a performance increase to the 2nd query over the 3rd? Also, whatever answer you give, if could you also comment on any differences when dealing inner joins instead (if there are any) that'd be great.

Thanks!

Adam Robinson · Accepted Answer · 2011-02-17 20:50:47Z

3

You can simply add your additional constraints to your left join (the word outer here is redundant, as all LEFT and RIGHT joins are automatically OUTER, and all unqualified joins are automatically INNER):

SELECT 
    c.CustID, 
    a.City, 
    a.Country 

FROM Customers AS c

LEFT JOIN Addresses AS a ON c.CustID = a.CustID AND a.Country = 'United States'

WHERE c.LastName = 'Jones'

As to your question regarding performance, the only real answer is to run both queries and compare the execution plans. That being said, the purely join-based syntax is substantially simpler, and will likely result in a simpler (and faster) execution plan.

answered Feb 17, 2011 at 20:50

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2011-02-17 20:52:19Z

3

Make the test for country part of the join condition.

SELECT c.CustID, a.City, a.Country 
    FROM Customers AS c
        LEFT OUTER JOIN Addresses AS a 
            ON c.CustID = a.CustID
                AND a.Country = 'United States'
    WHERE c.LastName = 'Jones'

answered Feb 17, 2011 at 20:52

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

HLGEM · Accepted Answer · 2011-02-17 20:51:13Z

1

A simpler form of your query would be:

SELECT c.CustID, a.City, a.Country
FROM Customers AS c     
LEFT OUTER JOIN Addresses AS a 
    ON c.CustID = a.CustID  AND a.Country = 'United States'     
WHERE c.LastName = 'Jones'

To see if it would be a better query than yours, I would suggest looking at the Execution plans.

The reason your query 2 does not work is by putting the condition in the where clause you have effectively made it an inner join.

answered Feb 17, 2011 at 20:51

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Comments

sjngm · Accepted Answer · 2011-07-15 23:44:42Z

1

SELECT c.CustID, a.City, a.Country
  FROM Customers AS c LEFT OUTER JOIN Addresses AS a ON c.CustID = a.CustID
 WHERE c.LastName = 'Jones'
   AND (a.Country = 'United States' OR a.Country IS NULL)

edited Jul 15, 2011 at 23:44

sjngm

13k16 gold badges90 silver badges118 bronze badges

answered Jul 15, 2011 at 21:10

DW123

111 bronze badge

1 Comment

CosmicGiant Over a year ago

While code-snippets are a way of demonstrating possible solutions, they are not always crystal-clear to everyone. Please always try to provide some explanation of what the solution is and how your code works towards achieving it.

Dhara · Accepted Answer · 2012-06-21 20:41:25Z

0

While you're looking into performance, you may want to try out the APPLY operator:

SELECT c.CustID, a.City, a.Country  
FROM Customers AS c 
    OUTER APPLY 
    (
        SELECT City, Country
        FROM Addresses  
        WHERE c.CustID = a.CustID 
            AND a.Country = 'United States' 
    ) AS a
WHERE c.LastName = 'Jones'

Sometimes, when the table on the right is large, and the optimizer thinks more than 5% or so of the records will be returned, you get a table scan and hash match with an outer join. However, an index seek and nested loop would be faster. I've used the APPLY operator in a number of these situations to dramatically improve performance.

I don't know how this will turn out in your case, but it might be worth comparing execution times and plans to see which is best.

edited Jun 21, 2012 at 20:41

Dhara

6,8052 gold badges34 silver badges48 bronze badges

answered Jun 21, 2012 at 4:17

Jace

1,55010 silver badges21 bronze badges

Collectives™ on Stack Overflow

T-SQL: Performance when using an inner query on a join

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related