CROSS JOIN

最近在讲到T-SQL查询的Join部分时，一下子没有想起来CROSS JOIN的用法，因为其实平常也确实基本不用到。特意找了一个例子，以供参考 CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。以下资料摘自： SQL CROSS JOIN will return all records where eac

最近在讲到T-SQL查询的Join部分时，虚拟主机，一下子没有想起来CROSS JOIN的用法，香港服务器，因为其实平常也确实基本不用到。特意找了一个例子，美国空间，以供参考

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。以下资料摘自：

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 1 1500

Jane 2 1000

David 2 2500

Paul 3 2000

James 3 2000

Table 2: Departments

DepartmentId DepartmentName

1 IT

2 Marketing

3 HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerName DepartmentId Scores DepartmentId DepartmentName

Jason 1 3000 1 IT

Irene 1 1500 1 IT

Jane 2 1000 1 IT

David 2 2500 1 IT

Paul 3 2000 1 IT

James 3 2000 1 IT

Jason 1 3000 2 Marketing

Irene 1 1500 2 Marketing

Jane 2 1000 2 Marketing

David 2 2500 2 Marketing

Paul 3 2000 2 Marketing

James 3 3000 2 Marketing

Jason 1 3000 3 HR

Irene 1 1500 3 HR

Jane 2 1000 3 HR

David 2 2500 3 HR

Paul 3 2000 3 HR

James 3 3000 3 HR