学步园

[转自]http://www.cnblogs.com/chenxizhang/archive/2008/11/10/1330325.html

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

Table 2: Departments

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments 

Result:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

另外贴一个CSDN的帖子,那Roy和小f的回答来讲解其中笛卡尔积的应用

[转自]http://topic.csdn.net/u/20111107/11/0f4581d4-1cfd-4913-ad0d-7b50bb6e7395.html#replyachor

现在有一个表student 结构如下：
id name class blood
1 张三 1 A
2 李四 2 C
3 王五 1 B
4 黄六 3 D
5 朱八 2 C
现在想查询出每个班的每种血型人数统计（假设只有ABCD四种血型）统计结果如下：
class blood num
1 A 1
1 B 1
1 C 0
1 D 0
2 A 0
2 B 0
2 C 2
2 D 0
3 A 0
3 B 0
3 C 0
3 D 1

if object_id('student') is not null
drop table student
go
create table student(
id int,
[name] varchar(50),
class varchar(50),
blood varchar(50))

go

insert student 
select 1,'张三','1','A' union all
select 2,'李四','2','C' union all
select 3,'王五','1','B' union all
select 4,'黄六','3','D' union all
select 5,'朱八','2','C'

go 
SELECT 
b.class,a.blood,COUNT(s.ID) AS num
FROM 
(SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' )a CROSS JOIN  (SELECT  DISTINCT class  from student)b
LEFT JOIN student s ON a.blood=s.blood AND s.class=b.class
GROUP BY b.class,a.blood
go 
drop table student

我们可以这样分析

由于要统计每个班级的血型及数量

看统计结果的前两列就可以知道是一个笛卡尔积的形式,三个班级与四种血型的乘积

所有班级在列表中都会出现.但是血型就不一定,搞不好所有的班级只有A.B.C,没有D血型,所以我们必须手动添加这四种血型(当然如果就3个班级你也可以手动添加)

所以手动生成两个表,里面各有班级和血型

第一步,生成blood列

SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'
作为一个表,我们可以查看一下这个表的内容
select * from (SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D') as a
/*
blood
---
A
B
C
D
*/

第二部,生成class列

由于给定的表中已经都包含所有的班级,所以我们只需要筛选去除重复的班级

select distinct class from student
/*
class
---
1
2
3
*/

第三步,笛卡尔积生成结果集

(SELECTDISTINCT classfrom student) as b
CROSSJOIN 
(SELECT'A'AS bloodUNIONALLSELECT'B'UNIONALLSELECT'C'UNIONALLSELECT'D') as a 
查看结果select * from ....
/*
class blood
--- ----
1 A 
1 B 
1 C 
1 D 
2 A 
2 B 
2 C 
2 D 
3 A 
3 B 
3 C 
3 D 
*/

第四步,统计数量

格式已经跟统计结果的前两列,但是我们还缺少什么.对了,是统计的血型的数量.

我们要统计的是每个班的每种血型,所以我们就要表连接查询student中的符合条件的作为一列并入到新表中

记住这里不能用内连接

如果是内连接

SELECT 
b.class,a.blood,COUNT(s.ID) AS num
FROM 
(SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' )a CROSS JOIN  (SELECT  DISTINCT class  from student)b
JOIN student s ON a.blood=s.blood AND s.class=b.class
GROUP BY b.class,a.blood
结果为
/*
class blood num
----    --    ---
1       A      1
1       B      1 
2       C      2 
3       D      1
*/

(具体原因可以查看另一篇文章:left
join 和 left outer join 的区别)

所以,这里必须用前三步生成的集合的左连接student中符合条件的.(这样就会包含左表有用的内容,右表没有的将用null补齐)

当然用student中符合条件的右连接前三步生成的集合也是一样的.

SELECT 
b.class,a.blood,COUNT(s.ID) AS num
FROM 
(SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' )a CROSS JOIN  (SELECT  DISTINCT class  from student)b
LEFT JOIN student s ON a.blood=s.blood AND s.class=b.class
GROUP BY b.class,a.blood

结果就会如题所示了.

讲完Roy的解法,那么小f的在5楼的解法

select
   b.class,
   b.blood,
   COUNT(a.blood) as num
from  
   tb a
full join
   (select * from (select distinct class from tb)a ,(select  distinct blood from tb) b)b
on
   a.class=b.class
and
   a.blood=b.blood
group by
   b.class,
   b.blood

小f用原先表全连接,全连接就会列出分别所有匹配两个表中的数据并生成行

(select * from (select distinct class from tb)a ,(select  distinct blood from tb) b)b

直接生成了我们之前 前三步的表,也是用的笛卡尔积原理,这里由于原数据中A.B.C.D血型都有了,所以小f没有考虑过多.

全连接后直接筛选出结果.