Not use Outer Apply - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not use Outer Apply

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2012-06-01 : 11:26:46

Hi All,

Is there a way to write the below query without using Outer Apply?

select b.order_id ,b.billing_id,transaction_type,marketing_id, b.billing_date, order_date
, activestartdate, activeenddate

FROM billing b
Outer Apply

( SELECT marketing_id, order_date
, activestartdate, sactiveenddate
from
orders o
inner join orderdetails do
on o.dimorder_id = do.dimorder_id
where
o.order_id = b.order_id
and b.billing_date between do.activestartdate and do.activeenddate
) o
order by order_id

Thanks for your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:32:07

yep use left join


select b.order_id ,b.billing_id,transaction_type,marketing_id, b.billing_date, order_date
, activestartdate, activeenddate

FROM billing b 
LEFT JOIN
( SELECT marketing_id, order_date
, activestartdate, sactiveenddate
from 
orders o 
inner join orderdetails do
on o.dimorder_id = do.dimorder_id
) o
on o.order_id = b.order_id 
and b.billing_date between do.activestartdate and do.activeenddate
order by b.order_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2012-06-01 : 13:19:21

Thanks Visakh16, it worked perfectly with the Left Join. Appreciate your help on this. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:40:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-02 : 11:24:52



For more Information :

Outer apply is similar to Left Outer Join 

cross apply is similar to Inner join 

Only difference is we can join a table with function .

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 13:55:11

its not exactly a join like more of correlated subquery. Its actually lot more than a join and in many scenarios will provide much better execution plan and performance than join especially when query involves some kind of correlated logic.most occasions apply operator can be used instead of join to yield the same execution plan but there are scenarios where it has its upper hand over joins especially when logic involves some kind of correlation. the same logic if implemented using join would result in RBAR problem and can hurt performance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources