T-SQL – Join two tables where the first table has no or multiple entries in other table or query

If you are trying to join a table of customers with a query of top 10 most recent invoices per customer, this is best achieved by using the APPLY operator. The APPLY operator involves one or two steps, depending on command used. This operator will help you join two tables where the first table has no or multiple entries in other table or query.

Using the CROSS APPLY operator, it will do a right table expression to the left table rows. This will join all the customers with the their top 10 most recent invoices. However, if they don’t have any invoices, they will not appear.

select C.CustomerId, subtab.invoiceid
FROM Customers as C
CROSS APPLY
(SELECT TOP 10 I.invoiceid, I.CustomerId,
FROM invoices as I
WHERE I.CustomerId = C.CustomerId
invoice BY I.invoiceid desc) as subtab;

The result of this query will be the top 10 most recent invoices for each customer, so long as each customer has one.

CustomerId

InvoiceId

Cust1

100

Cust1

99

Cust2

101

Cust2

98

Cust2

97

Using the OUTER APPLY operator, will do the same as a CROSS APPLY but it will show ALL customers, even ones without invoices. In the place of the invoiceid, the database will return NULL.

select C.CustomerId, subtab.invoiceid
FROM Customers as C
OUTER APPLY
(SELECT TOP 10 I.invoiceid, I.CustomerId,
FROM invoices as I
WHERE I.CustomerId = C.CustomerId
invoice BY I.invoiceid desc) as subtab;

The result of this query will be that you will have a list of all customers and where a customer has one or more invoices, you will have the top 10 per customer.

CustomerId

InvoiceId

Cust3

NULL

Cust1

100

Cust1

99

Cust2

101

Cust2

98

Cust2

97