Difference between CROSS JOIN and CROSS APPLY

Posted by Muhsinathk under Sql Server on 6/14/2012 | Points: 10 | Views : 31774 | Status : [Member] | Replies : 10
Hi,
What is the difference between CROSS JOIN and CROSS APPLY?
I want to know more about it..




Responses

Posted by: Sudarsan on: 6/14/2012 [Member] Starter | Points: 25

Up
0
Down
Both r same it is multiply first table's first cor with second tables every rows getting the output

Syntex: select table1.column1,table2.column2,........
table2.column1,table2.column2,........
from table1 cross join table2


Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/14/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you @Sudarsan
But when i use
SELECT * FROM employee CROSS JOIN department where employee.LastName=Jones;
i am getting an error 'Invalid column name 'Jones'.'
I want to apply some conditions on CROSS JOIN..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 6/14/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
hI

CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.

2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)

CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

2.The table-valued function acts as the right input and the outer table expression acts as the left input.

3.The right input is evaluated for each row from the left input and the rows produced are combined for the
final output.

Reference: MSDN

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/14/2012 [Member] Bronze | Points: 25

Up
0
Down
Ok..
Can i apply some condition on CROSS APPLY by using WHERE clause?

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 6/14/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
In CROSS APPLY also You can apply WHERE clause!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudarsan on: 6/14/2012 [Member] Starter | Points: 25

Up
0
Down
that is sure

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudarsan on: 6/14/2012 [Member] Starter | Points: 25

Up
0
Down
check it

SELECT * FROM emp CROSS JOIN DEPT where emp.EName='Jones';

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/15/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 6/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Cross join is an cartesion product,its matrix format,Cross join not support where condition

select * from Table1 cross join Table 2

Hope this will help

Regards

Sriram.R

sriram

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/30/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you @Sriramanda We can add conditions on Cross join..
Please see @Sudarsan comments

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response