APPLY operator - how does it decide which rows are a match between the two sets?

Question

When using a JOIN, it is clear exactly what is deciding whether or not the rows will match, e.g. ON a.SomeID1=b.SomeID1. So the only rows returned will be ones where there is a matching 'SomeID1' in the tables referenced by aliases A and B.

My initial thought was that, when using APPLY, a WHERE clause is typically placed within the right-hand query, to provide similar functionality to the ON clause of a JOIN.

However, I see many SQL queries that do not include a WHERE in the right-hand query when using APPLY. So won't this mean that the resulting rows will just be the product of the number of rows from both tables?

What logic determines which rows will match between the left and right queries when using APPLY?

I have tried many blog posts, answers on here and even YouTube videos, but none of the explanations have 'clicked' with me.

Have you seen this? https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ Remember that APPLY is an SQL Server extension. You won't find it in most implementations. — Tim Roberts, Mar 10 '21 at 18:21
The key to understanding apply is that its not matching, its applying, it calculating new data based on the existing data which is quite different to joining on separate data. — Dale K, Mar 10 '21 at 19:34
In APPLY each row of the left table is CROSS JOINed with the right table that is a function of it. INNER JOIN ON is CROSS JOIN WHERE. CROSS JOIN is INNER JOIN ON TRUE. PS You're just asking for another presentation of APPLY to not understand, without our knowing what you don't understand. Read an authoritative introduction/tutorial/manual & ask 1 specific researched non-duplicate question where 1st stuck. When you see unexpected examples/results/output, say what you expected instead & why, with justification referencing quoted authoritative documentation. PS Find a textbook. Dozens are online. — philipxy, Mar 10 '21 at 20:09
Before considering posting please read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Mar 10 '21 at 20:14
[CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) — philipxy, Mar 10 '21 at 20:40

score 1 · Answer 1 · answered Mar 10 '21 at 18:18

The apply operator (in databases that support it) implements a type of join called a lateral join.

For me, the best way of understanding it starts with a correlated subquery. For instance:

select a.*,
       (select count(*) 
        from b
        where b.a_id = a.a_id
--------------^
       ) as b_count
from a;

The subquery is counting the number of matching rows in b for each row in a. How does it do this? The correlation clause is the condition that maps the subquery to the outer query.

Apply works the same way:

select a.*, b.b_count
from a outer apply
     (select count(*) as b_count
      from b
      where b.a_id = a.a_id
------------^
     ) b;

In other words, the correlation clause is the answer to your question.

What is the difference between a lateral join and a correlated subquery? There are three differences:

A lateral join can return more than one row.
A lateral join can return more than one column.
A lateral join is in the FROM clause so the returned columns can be referenced multiple times in the query.

score 0 · Answer 2 · answered Mar 10 '21 at 23:28

Further to Gordon's excellent answer:

APPLY does not need to be correlated (ie that it uses columns from the outer query), the key is that it is lateral (it returns a new resultset for each row).

So starting with a base query:

select c.*
from customer c;

Example Resut:

Id	Name
1	John
2	Jack

The idea is to apply a new resultset to this. In this case, we only want a single row (a grouped-up count) to apply for each existing row.

Note the where correlation, we use an outer reference

select c.*, o.Orders
from customer c
outer apply
     (select count(*) as Orders
      from [order] o
      where o.c_id = c.id
     ) o;

Id	Name	Orders
1	John	2
2	Jack	0

We can, however, return multiple results. In fact, we can return anything we like, and place arbirtrary filters on the result:

select c.*, t.*
from customer c
outer apply
     (select 'Thing1' thing
     union all
     select 'Thing2'
     where c.Name = 'Jack'
     ) t;

Id	Name	thing
1	John	Thing1
1	John	Thing2
2	Jack	Thing1

Note how the row containing John got doubled up, based on the filter. Note also that the first half of the union has no outer reference.

See also this answer for further APPLY tricks.

APPLY operator - how does it decide which rows are a match between the two sets?

2 Answers2