笛卡尔积/内连接/外连接

笛卡尔积、内连接和外连接的关系

笛卡尔积与内连接

笛卡尔积又叫笛卡尔乘积，由一个叫笛卡尔的人提出的，为两个集合相乘得到的结果。比如：

A={1,2},B={a,b,c},笛卡尔积就是{(1,a),(1,b),(1,c),(2,a),(2,b),(2,c)}，MySQL中的笛卡尔积（交叉连接）也是类似的。

笛卡尔积结果集：行数为两表的行数相乘，列数为两个表的所有列。

内连接结果集：行数是满足关联条件的行对数，列数是满足条件的两表行中所选取的两表列数。

empt表：

dept表：

如果我们执⾏如下语句，将产⽣所有⾏的联合集即笛卡尔积

Select * From empt,dept;
或者
Select * From empt JOIN dept;
或者
Select * From empt INNER JOIN dept;

显然，以上结果是没有什么意义的，一般会对以上查询增加条件以产⽣有价值的数据集合，比如关联条件

内连接：

SELECT a.*,b.id,b.name FROM empt a JOIN dept b ON a.dept_id = b.id;

再者，笛卡尔积是对两表进行全表扫描,会产生大量冗余数据，查询效率较低。而内连接利用连接条件进行筛选，可以减少不必要的数据处理，查询效率较高。若on条件是非唯一字段，则会出现局部笛卡尔积。

笛卡尔积产生条件：
1.省略多个表的连接条件（或关联条件）。

例如：没有关联条件

2.连接条件（或关联条件）无效。

例如：on条件是非唯一字段，会出现局部笛卡尔积

3.所有表中的所有行互相连接。

内连接和外连接

内连接（INNER JOIN,INNER可以省略），它只返回两个表中满足连接条件的行，内连接的优点是操作简单、效率较高，缺点是可能会丢失不符合连接条件的数据。

外连接（OUTER JOIN,OUTER 可以省略），包括左(外)连接、右(外)连接、全(外)连接，它返回两个表中的所有行（比如全连接），并根据连接条件进行匹配，两边记录空缺的地方均为NULL。与内连接不同，外连接不会剔除不符合连接条件的行，而是将它们保留在结果集中。外连接的优点是能够保留更多的数据，缺点是操作相对复杂且效率较低。

左连接：

SELECT a.name,a.dept_id,b.id,b.name FROM empt a LEFT JOIN dept b ON a.dept_id = b.id;

右连接：

SELECT a.name,a.dept_id,b.id,b.name FROM empt a RIGHT JOIN dept b ON a.dept_id = b.id;

Oracle支持的全连接(FULL JOIN)，MySQL却不支持全连接，但可以使用UNION ALL进行模拟全连接。需要注意的是，ALL用在UNION子句里，省略与否的输出结果是不一样的。

SELECT a.name,a.dept_id,b.id,b.name FROM empt a LEFT JOIN dept b ON a.dept_id = b.id
UNION ALL
SELECT a.name,a.dept_id,b.id,b.name FROM empt a RIGHT JOIN dept b ON a.dept_id = b.id;

有ALL，将保留所有的记录行，不会剔除有重复的行

SELECT a.name,a.dept_id,b.id,b.name FROM empt a LEFT JOIN dept b ON a.dept_id = b.id
UNION
SELECT a.name,a.dept_id,b.id,b.name FROM empt a RIGHT JOIN dept b ON a.dept_id = b.id;

省略ALL关键字，系统会自动去重。

若全连接的两条sql是同一张表的不同条件，则结果数据以第一条sql为基准，且两条sql的select字段顺序最好相同，否则结果数据容易错乱，可读性差。

相同顺序：

select * from empt where dept_id = 1
union
select id，name,gender,dept_id,is_enable from empt where dept_id = 2；

不同顺序：

select * from empt where dept_id = 1
union
select name,id,gender,dept_id,is_enable from empt where dept_id = 2；