[SOLVED] Why CROSS APPLY and INNER JOIN returns different result

Why CROSS APPLY and INNER JOIN returns different result

Why using cross apply return extra row. Isn't it should work similar to INNER JOIN? The result I'd expect is

QuoteID  controlNo  etc            MaxQuoteID   COntrolNo
10101     1111     something15     10101        1111

Sample Data:

-- create first example table
drop table if exists #test1
create table #test1 (QuoteID int, controlNo int, etc varchar(100))
insert into #test1 values 
(1111, 1111,'something1'),
(10101, 1111,'something15'),
(2222, 2222,'something2'),
(3333, 3333,'something3'),
(3333, 30303,'something35'),
(4444, 4444,'something4')
select * from #test1

--create second example table
drop table if exists #test2
create table #test2 (QuoteID int, ControlNo int)
insert into #test2 values 
(1111,1111),
(10101,1111)
select * from #test2

-- resutl query 1. This one works as expected
select * 
from #test1 t
inner join (select max(QuoteID) as MaxQuoteID, COntrolNo from #test2 group by ControlNo) tt ON t.QuoteID = tt.MaxQuoteID

-- But why this one doesnt work?
select * 
from #test1 t
cross apply 
(
-- subquery returns a single quoteid 10101, which is what I need
select max(QuoteID) as QuoteID
from #test2 tt
where tt.QuoteID = t.QuoteID
group by ControlNo
) a

Solution

Both of the query are not the same.

In Query 1, you wanted the max(QuoteID) group by controlNo

In Query 2, you are getting the max(QuoteID) for each controlNo

If you wanted equivalent using CROSS APPLY, should be

select  * 
from    #test1 t
        cross apply 
        (
            select  max(tt.QuoteID) as QuoteID, tt.controlNo
            from    #test2 tt
            group by tt.controlNo
            having  max(QuoteID) = t.QuoteID
        ) a