GROUP BY in CROSS APPLY

Question

Let us have two tables

create table A (
  fkb int,
  groupby int
);

create table B (
  id int,
  search int
);

insert into A values (1, 1);
insert into B values (1, 1);
insert into B values (2, 1);

then the following query

select B.id, t.max_groupby - B.search diff
from B
cross apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
) t

return the expected result as follows

id  diff
---------
1   0
2   NULL

However, when I add the group by A.fkb into the cross apply, the B row where the corresponding A.fkb does not exist, disappear.

select B.id, t.max_groupby - B.search diff
from B
cross apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
  group by A.fkb
) t

I was testing on SQL Server as well as on PostgreSQL (with cross join lateral instead of cross apply). Why the group by makes the row disappear? It seems that the cross apply behaves as an outer join in the first case and as an inner join in the latter case. However, it is not clear to me why.

`select B.id, (select max(A.groupby) from A where A.fkb = B.id) - B.search diff from B` I think you expect the result of this query. — Ahmet Remzi EKMEKCI, Apr 11 '19 at 08:02

score 3 · Accepted Answer · answered Apr 11 '19 at 07:55

You can see this when you look at the result of the inner query separately:

select max(A.groupby) max_groupby
from A 
where A.fkb = 2;

returns a single row with max_groupby = null:

max_groupby
-----------
     (null)

However as there is no row with A.fkb = 2 grouping by it yields an empty result which you can see when you run:

select max(A.groupby) max_groupby
from A 
where A.fkb = 2
group by A.fkb

and thus the cross join does not return return rows for fkb = 2

You need to use an outer join in order to include the row from B.

In Postgres you would have to write this as:

select B.id, t.max_groupby - B.search diff
from B
  left join lateral ( 
    select max(A.groupby) max_groupby
    from A 
    where A.fkb = B.id 
    group by A.fkb
  ) t on true

I don't know what the equivalent to left join lateral would be in SQL Server.
on true would need to be written as on 1=1.

Excellent answer, thank you! The equivalent of left join lateral in SQL Server is `outer apply`. — Radim Bača, Apr 11 '19 at 08:00

StepUp · Answer 2 · 2019-04-11T08:01:46.823

1

It happens because:

GROUP BY returns nothing when A.fkb = 2
without GROUP BY returns NULL

So your query CROSS APPLY returns different results.

select B.id, t.max_groupby - B.search diff
from B
outer apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
  group by A.fkb
) t

OUTPUT:

id  diff
1   0
2   NULL

edited Apr 11 '19 at 08:01

answered Apr 11 '19 at 07:49

StepUp

36,391
15
88
148

Thanks for your answer. My question was: why `group by` in `cross apply` makes a difference in the result? In other words, why the introduction of the `group by` alter the semantics of the join? – Radim Bača Apr 11 '19 at 07:53

GROUP BY in CROSS APPLY

2 Answers2