OUTER APPLY Walkthrough

Given a task to display the nearest immediate elder brother's birthdate of a given Person, we might come up with subquery.

create table Member
(
 Firstname varchar(20) not null, 
 Lastname varchar(20) not null,
 BirthDate date not null unique
);

insert into Member(Firstname,Lastname,Birthdate) values
('John','Lennon','Oct 9, 1940'),
('Paul','McCartney','June 8, 1942'),
('George','Harrison','February 25, 1943'),
('Ringo','Starr','July 7, 1940');

Assuming that no persons share the same birthdate, this is how we might code it in subquery:

select m.*,
  
 ElderBirthDate = 
  (select top 1 x.BirthDate 
  from Member x 
  where x.BirthDate < m.BirthDate 
  order by x.BirthDate desc)
  
from Member m
order by m.BirthDate;

Now, some nasty users requested for more information, she wanted to see that elder person's Firstname too; as a good developer you are, of course you will comply. Here's your new query:

select m.*,

 ElderBirthDate = 
  (select top 1 x.BirthDate 
  from Member x 
  where x.BirthDate < m.BirthDate 
  order by x.BirthDate desc),
 ElderFirstname = 
  (select top 1 x.Firstname 
  from Member x 
  where x.BirthDate < m.BirthDate 
  order by x.BirthDate desc)
  
from Member m
order by m.BirthDate

Output:

Firstname            Lastname             BirthDate  ElderBirthDate ElderFirstname
-------------------- -------------------- ---------- -------------- --------------------
Ringo                Starr                1940-07-07 NULL           NULL
John                 Lennon               1940-10-09 1940-07-07     Ringo
Paul                 McCartney            1942-06-08 1940-10-09     John
George               Harrison             1943-02-25 1942-06-08     Paul

(4 row(s) affected)

Then a day after, she requested to add the Lastname, she deemed that it is nice to have that information on the report too. Things are getting hairy isn't it? We are violating DRY principle, if we are using subquery approach.


Enter OUTER APPLY, this neat technology is ought to be present in all RDBMS, unfortunately(if you expect that it is available on all RDBMS at the time of this writing) this is available on SQL Server only:

select m.*, elder.*
from Member m
outer apply
(
 select top 1 ElderBirthDate = x.BirthDate
 from Member x 
 where x.BirthDate < m.BirthDate 
 order by x.BirthDate desc
) as elder
order by m.BirthDate

Compared to subquery, at first glance it doesn't seem to add much in terms of value; but where it shines is it can pick up all the columns on the matched condition. Now back to the requested new column on report by our dear user, we can neatly add those column(s) if we are using OUTER APPLY instead:

select m.*, elder.*
from Member m
outer apply
(
 select top 1 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
 from Member x 
 where x.BirthDate < m.BirthDate 
 order by x.BirthDate desc
) as elder
order by m.BirthDate

Firstname            Lastname             BirthDate  ElderBirthDate ElderFirstname
-------------------- -------------------- ---------- -------------- --------------------
Ringo                Starr                1940-07-07 NULL           NULL
John                 Lennon               1940-10-09 1940-07-07     Ringo
Paul                 McCartney            1942-06-08 1940-10-09     John
George               Harrison             1943-02-25 1942-06-08     Paul

(4 row(s) affected)

Not only there is less friction on modifying our query based on user's requests when we uses OUTER APPLY, our OUTER APPLY query scales nicely too:

Now there's a new request in town to display the person's two immediate elder brothers; if we are using subquery, we might cringe at the thought of rewriting our query just to facilitate such whimsical requirement. But hey, we are using OUTER APPLY, you can laugh in triumph rather than quivering in pain, just modify the TOP 1 to TOP 2 to support that requirement. Convenient isn't it?

select m.*, elder.*
from Member m
outer apply
(
 select top 2 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
 from Member x 
 where x.BirthDate < m.BirthDate 
 order by x.BirthDate desc
) as elder
order by m.BirthDate, elder.ElderBirthDate desc

Output:

Firstname            Lastname             BirthDate  ElderBirthDate ElderFirstname
-------------------- -------------------- ---------- -------------- --------------------
Ringo                Starr                1940-07-07 NULL           NULL
John                 Lennon               1940-10-09 1940-07-07     Ringo
Paul                 McCartney            1942-06-08 1940-10-09     John
Paul                 McCartney            1942-06-08 1940-07-07     Ringo
George               Harrison             1943-02-25 1942-06-08     Paul
George               Harrison             1943-02-25 1940-10-09     John

(6 row(s) affected)

Live test: http://www.sqlfiddle.com/#!3/19a63/1