CODEWITHSUNDEEP
sql
stefanstick
stefanstick

Reputation: 31

What is outer apply in SQL?

select *
from Kosten_Test a
left join T_Pflege_Parameter pv on pv.Parameterbezeichnung = 'Dummy' and pv.Parametereigenschaft = 'Dummy3'
left join T_Pflege_Parameter pp on pp.Parameterbezeichnung = 'Dummy2' and pp.Parametereigenschaft = a.Herkunft
outer apply( select max(VarianteID) as VarianteID, MerkmalTyp, MerkmalWert
from Test2
where MerkmalTyp = pv.Parameterwert and MerkmalWert = sku
group by MerkmalWert, MerkmalTyp

union

select max(VarianteID) as VarianteID, MerkmalTyp, MerkmalWert
from Testvariante_ASIN_SKU
where MerkmalTyp = pv.Parameterwert and MerkmalWert = a.asin
group by MerkmalWert, MerkmalTyp
) vm
left join (select distinct bundleid from T_Archiv_BundleKomponente) bk on bk.BundleID = vm.VarianteID

Because of the Outer Apply Statement i become always double results. Who can help?

Upvotes: 3

Views: 6320

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

Outer apply is not like a LEFT JOIN. It will apply all the values from the OUTER APPLY Statement to the data joined against.

If your query without the outer apply returns 5 rows and the outer apply query returns 5 rows then the resulting dataset would contain 25 records with each record from the outer apply joined to each row of the other data.

Often times data would be condensed down using aggregations of the values returned from the outer apply query grouped inside of the main query.

Example -

Q1
----
A
B
C

OuterApplyQuery
-----------------
1
2
3

SELECT * FROM Q1 OUTER APPLY (SELECT * FROM OuterApplyQuery) AS X

Result
---------
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

Upvotes: 3

Related Questions