SQL Server – Selecting Top 1 in a Left Join

This scenario doesn’t come up too often, but the other day I had a requirement to do a left join through a one to many relationship but only get 1 result from the right side of the join. This kind of scenario can often be accomplished using sub selects, but in this case I needed access to multiple columns so a sub select would be out of the question.

The solution was to do an OUTER APPLY instead of a LEFT JOIN. An outer apply allows you to write a sub query referencing the ID from the original table just like a sub select. However as this is effectively a kind of join you have access to multiple columns.

Example Scenario

Imagine you have a table of customers and a table of addresses and each customer can have multiple addresses.

Our tables will look like this:

Customer (CustomerID, FirstName, LastName)

Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)

The task is to get a list of customers and their most recent address.

Solution

By using an OUTER APPLY we can join the 2 tables and get the most recent address like this: 

SELECT c.*, la.*
FROM Customer c
OUTER APPLY 
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.CustomerID = c.CustomerID
    ORDER BY a.DateAdded DESC
    ) AS la

5 thoughts on “SQL Server – Selecting Top 1 in a Left Join”

  2. Connie McBride says:
    Reply

    If you have 9 k customers, and 25 k addresses, and you write it like this:
    SELECT c.*, la.*
    FROM Customer c
    OUTER APPLY
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.ID = c.AddressID
    ORDER BY a.DateAdded DESC
    ) AS la
    is there any benefit vs using a left outer join (since it will only link to one record?

    • timgriff84 says:
      Reply

      No you should use a left join, an outer apply would be significantly worse.

      You would only use the outer apply in a one to many relation and you want to limit the results to 1 row from the many side.

      • Mehul says:

        Respectfully disagree, TIMGRIFF84. You are right about outer apply, but crucial reason to use outer apply is when u need access to outer table e.g. customer in this case. Left join doesn’t recognize the outer table. I am not expert by any means, but this is what I have learnt based on experience. Do correct me guys, if wrong.

Leave a reply to Connie McBride Cancel reply