CROSS JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.


CROSS JOIN or CARTESIAN PRODUCT is a less frequently used or rarely used JOIN in SQL Server.


CARTESIAN PRODUCT will match each record from left side table to each record is right side table . That is CROSS JOIN will be a many to many relationship.


 Syntax for CROSS JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A> , <Right Side Table> <B>



JOIN keyword need not be used in CROSS JOIN.
In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.


Example:


Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.















Query to get the CARTESIAN PRODUCT of STUDENT and LANGUAGES table is:

Query:

SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S   ,OUTER JOIN LANGUAGES L
Result Set:














CROSS JOIN or CARTESIAN PRODUCT can be queried using CROSS JOIN Keyword also. Here is the syntax for CARTESIAN PRODUCT using CROSS JOIN Keyword:
Syntax for CROSS JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A> 
CROSS JOIN  <Right Side Table> <B>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name

ON keyword will not be used in conjunction with CROSS JOIN.

Query to retrieve CARTESIAN PRODUCT using the above Syntax is


SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
CROSS OUTER JOIN LANGUAGES L

Result Set:











Note: 

If Table A have 'x'rows and Table B have 'y' rows then , result set of CROSS JOIN of tables A and B will have X*Y rows.
In the above example STUDENT table have 10 rows and LANGUAGES table have 18 rows.
Result set of CROSS JOIN or CARTESIAN PRODUCT between these 2 tables have 10*8=180 Rows.

To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

This entry was posted in , . Bookmark the permalink.

Leave a reply