title: GROUP BY in CROSS APPLY
body: <p>Let us have two tables</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>then the following query </p>
  
  <pre><code>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
  </code></pre>
  
  <p>return the expected result as follows</p>
  
  <pre><code>id  diff
  ---------
  1   0
  2   NULL
  </code></pre>
  
  <p>However, when I add the <code>group by A.fkb</code> into the cross apply, the <code>B</code> row where the corresponding <code>A.fkb</code> does not exist, disappear. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I was testing on SQL Server as well as on PostgreSQL (with <code>cross join lateral</code> instead of <code>cross apply</code>). Why the <code>group by</code> makes the row disappear? It seems that the <code>cross apply</code> 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.</p>