CROSS JOIN

简介: 原文:CROSS JOIN最近在讲到T-SQL查询的Join部分时,一下子没有想起来CROSS JOIN的用法,因为其实平常也确实基本不用到。特意找了一个例子,以供参考 CROSS JOIN又称为笛卡尔乘积,实际上是把两个表乘起来。
原文: 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
目录
相关文章
|
5月前
|
关系型数据库
INNER JOIN的使用
INNER JOIN的使用
29 0
|
7月前
openGauss向量化Merge Join--semi join
openGauss向量化Merge Join--semi join
50 0
|
4月前
|
SQL 测试技术
软件测试|深入理解SQL CROSS JOIN:交叉连接
软件测试|深入理解SQL CROSS JOIN:交叉连接
36 0
|
7月前
openGauss向量化Merge Join--inner join
openGauss向量化Merge Join--inner join
48 0
openGauss向量化Merge Join--inner join
inner join
INNER JOIN 关键字 在表中存在至少一个匹配时,INNER JOIN 关键字返回行。 INNER JOIN 关键字语法 SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name 注释:INNER JOIN 与 JOIN 是相同的。 原始的表 (用在例子中的): "Persons" 表: Id_P LastName FirstName Address City 1 Adams John Oxford Street L
92 0
|
SQL Cloud Native 关系型数据库
排除法,一个一个 join
排除法,一个一个 join
67 0
排除法,一个一个 join
|
SQL 关系型数据库 MySQL
MySQL - LEFT JOIN、RIGHT JOIN、INNER JOIN、CROSS JOIN、FULL JOIN
MySQL - LEFT JOIN、RIGHT JOIN、INNER JOIN、CROSS JOIN、FULL JOIN
310 0
MySQL - LEFT JOIN、RIGHT JOIN、INNER JOIN、CROSS JOIN、FULL JOIN
|
SQL 关系型数据库 MySQL
Accelerating Queries with Group-By and Join By Groupjoin
这篇paper介绍了HyPer中引入的groupjoin算子,针对 join + group by这种query,可以在某些前提条件下,在join的过程中同时完成grouping+agg的计算。 比如用hash table来实现hash join和group by,就可以避免再创建一个hash table,尤其当join的数据量很大,产生的group结果又较少时,可以很好的提升执行效率。
309 0
Accelerating Queries with Group-By and Join By Groupjoin
join
  public class JoinTest { //thread.Join把指定的线程加入到当前线程,可以将两个交替执行的线程合并为顺序执行的线程。 //比如在线程B中调用了线程A的Join()方法,直到线程A执行完毕后,才会继续执行线程B。
809 0