Category: SQL Server

05 Jul 2006 06:04

Joined:	20/05/2005
Level:	Silver
Points:	50

Points:	2

why we use cross join in sqlserver?

The forum thread has not been reviewed by Editors yet. Readers are advised to use their best judgement before accessing this thread.
This thread will be reviewed shortly.
If you think this thread contain inappropriate content, please report to webmaster.

what is the need of cross join .where we use this .

#61854

05 Jul 2006 06:25

Joined:	01/02/2006
Level:	Gold
Points:	440

Points:	2

A CROSS JOIN Combines records from two tables without any conditions.

in other words:
A CROSS JOIN operator is analogous to an INNER JOIN operator without a condition.

A CROSS JOIN operator is frequently used with a WHERE clause that filters the resulting records using criteria that would be not be used within an INNER JOIN.

A CROSS JOIN operator is also frequently used with the same table. In this case, it is often necessary to provide an alternate name for one of the copies of the table, so that the names of columns in the two copies of the table are no longer ambiguous.

Example

This example uses the CROSS JOIN operator and a WHERE clause to select the orders that were shipped later than those orders made after them (the DISTINCT operator collapses records for the same order into one record):

SELECT DISTINCT [Orders].[Order ID] FROM [Orders] CROSS JOIN [Orders] AS [Copy] WHERE [Orders].[Order Date] < [Copy].[Order Date] AND [Orders].[Shipped Date] > [Copy].[Shipped Date];

#61885

05 Jul 2006 07:31

Joined:	23/02/2006
Level:	Gold
Points:	468

Points:	2

cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. 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. This is an example of a Transact-SQL cross join:

USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC

Thanks & Regards,
Jugal Shah
http://dbsconsultant.blogspot.com/
http://sqldbpool.wordpress.com/

#63734

19 Jul 2006 11:01

Joined:	23/02/2006
Level:	Gold
Points:	468

Points:	2

A CROSS JOIN Combines records from two tables without any conditions.

in other words:
A CROSS JOIN operator is analogous to an INNER JOIN operator without a condition.

A CROSS JOIN operator is frequently used with a WHERE clause that filters the resulting records using criteria that would be not be used within an INNER JOIN.

A CROSS JOIN operator is also frequently used with the same table. In this case, it is often necessary to provide an alternate name for one of the copies of the table, so that the names of columns in the two copies of the table are no longer ambiguous.

Example

This example uses the CROSS JOIN operator and a WHERE clause to select the orders that were shipped later than those orders made after them (the DISTINCT operator collapses records for the same order into one record):

SELECT DISTINCT [Orders].[Order ID] FROM [Orders] CROSS JOIN [Orders] AS [Copy] WHERE [Orders].[Order Date] < [Copy].[Order Date] AND [Orders].[Shipped Date] > [Copy].[Shipped Date];

Thanks & Regards,
Jugal Shah
http://dbsconsultant.blogspot.com/
http://sqldbpool.wordpress.com/

why we use cross join in sqlserver?

About dotnetspider.com

Quick Links

General

Services