SQL CROSS JOIN

最近在讲到T-SQL查询的Join部分时，一下子没有想起来CROSS JOIN的用法，因为其实平常也确实基本不用到。特意找了一个例子，以供参考

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。以下资料摘自：http://www.sqlguides.com/sql_cross_join.php

SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SQL CROSS JOIN syntax:

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

OR

SELECT * FROM [TABLE 1], [TABLE 2]

EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerName

DepartmentId

Scores

Jason

1

3000

Irene

1500

Jane

2

1000

David

2500

Paul

3

2000

James

Table 2: Departments

DepartmentName

IT

Marketing

HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result: