SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outer Apply and Left Join differance


Outer Apply and Left Join differance

Author
Message
Balaji M R
Balaji M R
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 49
Want to know the difference between Outer Apply and Left Join.
For the same query if i use Outer Apply, query output will be faster but in some DBs Outer Apply takes log period for returning details.

i am not seeing consistency in the data output time.
Is there any restriction due to the SQL Server 2005/2008/2008 R2 for using Cross Apply in queries.

Thanks & Regards,
Balaji
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 798
The main difference between the join and the apply operator is that the apply operator can be used to "join" the result from a function.

If you use the apply-operator using a subselect it might be the same than using the join-operator.
In my experience the apply-operator is more likely to use the subselect as a sort of loop and executes the subselect for each outer row. But also this: not always.

If you expierence differences in your performance you have to look at the execution plans.
If you are not familiar with reading execution plans save them and post them here (save it as am XML, NOT as a screenshot.
You can also try to create an example and post it here so we can have a detailled look at it.
Balaji M R
Balaji M R
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 49
Thanks for reply....

the main difference i have seen in these are

Left Join: i need to prepare the query as table (Temporary Table) and actual join will be provided after filtering of data using "ON"

Cross Apply: i can prepare the Temporary Table in that i can make use of my parent table reference.

Example:

Left Join:
Select r.Name, r.EmailID, ISNULL(j.Employer) as PresentEmployer From CandidateInfo r WITH (NOLOCK)
LEFT JOIN (Select CandID, Employer From JOB_EMPLOYER WITH (NOLOCK)) as j ON j.CandID = r.ID

OUTER APPLY:
Select r.Name, r.EmailID, ISNULL(j.Employer) as PresentEmployer From CandidateInfo r WITH (NOLOCK)
OUTER APPLY (Select Employer From JOB_EMPLOYER WITH (NOLOCK) Where CandID = r.ID) as j

These two sample queries are examples in which i actually use Pivot output as table and that will be joining with parent table for getting details.

As per my observation in SQL Server 2005, i was not seen any greater performance when i user CROSS APPLY but query output was faster and worker table load was also pretty low when i user CROSS APPLY in SQL Server 2008.

But same query was not performing properly if use this logic for other combination.
So i want to know that is there any dependency or specific formation where and when we can use CROSS APPLY.
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 798
The apply-operator was introduced to "join" the result of a function to a resultset. That was not possible before the apply-operator.
When using a table expression for joining there is no general answer whether you should use the one or the other.

The only thing you can do is: analyse the execution plans!

If you need some more information about your example queries, give us your execution plans or provide some example data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search