Sunday, December 18, 2011

Cross Join in SQL-Server

Posted by Rahul Kharde at 10:33 PM

Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has n rows and table B has m rows, a CROSS JOIN will result in n X m rows.
There is no Where Condition used in cross join

SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
OR
SELECT * FROM [TABLE 1], [TABLE 2]


Example

CREATE TABLE [dbo].[Student]
(
[StudID] [int] IDENTITY(1,1) NOT NULL,
[StudName] [varchar](50)
)
CREATE TABLE [dbo].[Division]
(
[DivisionID] [int] IDENTITY(1,1) NOT NULL,
[DivisionName] [varchar](50)
)
INSERT INTO Student(StudName) VALUES('ABC')
INSERT INTO Student(StudName) VALUES('XYZ')
INSERT INTO Division(DivisionName) VALUES('Division 1')
INSERT INTO Division(DivisionName) VALUES('Division 2')
INSERT INTO Division(DivisionName) VALUES('Division 3')


SELECT * FROM Student
CROSS JOIN Division


OUTPUT
We have two table Student and Division corresponding 2 and 3 rows in table cross join return

2 X 3 = 6 Rows



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top