欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

CROSS JOIN

程序员文章站 2022-04-30 18:09:48
...

最近在讲到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

相关标签: CROSS JOIN