CROSS JOIN

杰克.陈 2017-12-13

SQL IT

原文: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 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
登录 后评论
下一篇
云栖号资讯小编
20700人浏览
2020-07-13
相关推荐
T-SQL基础(二)之关联查询
1339人浏览
2018-11-14 22:27:12
SQL中join的用法
1125人浏览
2018-07-27 08:44:52
join的用法
1078人浏览
2009-11-07 22:05:00
Oracle 连接查询
908人浏览
2014-02-08 16:34:00
cross join
813人浏览
2017-11-09 20:35:00
0
0
0
984