Consider the following scenario: You need to produce a query which joins several tables but from one of the tables you only need the last matching record. This can be achieved by using the T-SQL OUTER APPLY operator. Consider the following simple database structure:

CREATE TABLE Clients
(
  client_id int NOT NULL PRIMARY KEY
  ,client_name varchar(30)
 ,client_address varchar(40)

)
CREATE TABLE Operators
(
  operator_id NOT NULL PRIMARY KEY
  ,operator_name varchar(30)
)
CREATE TABLE Support
(
 support_id int NOT NULL PRIMARY KEY
 ,client_id int NOT NULL
 ,assigned_operator_id NOT NULL 
 ,support_description varchar(30)
 ,support_detail text
 ,support_open_date datetime
 ,support_close_date datetime
)
CREATE TABLE Notes
(
 note_id int NOT NULL PRIMARY KEY
 ,support_id NOT NULL
 ,entering_operator_id NOT NULL
 ,note_date_time datetime NOT NULL
 ,note_detail text
)

Let’s say we want to create a query which will list the support ticket id,support ticket description,assigned operator name,client name,support ticket open date,and the last note entered against the support ticket for all open support tickets. The following query using outer apply produces the desired results:

SELECT support_id, support_desc, operator_name, client_name, support_open_date, note_date_time, note_detail 
FROM Support
INNER JOIN operator ON assigned_operator_id=operator_id
INNER JOIN client ON Support.client_id=client.client_id
OUTER APPLY (SELECT top 1 * FROM Notes 
                    WHERE Notes.support_id = Support.support_id
                    ORDER BY note_date_time desc) Notes
WHERE support_close_date IS NULL

The outer apply statement will select the top (read last because we are ordering by descending date) support note if any exists. The support ticket will still be returned even if a support note doesn’t exist.