文章目录
连接分为:内连接、外连接、交叉连接
1. 内连接
内连接:也称为等值连接,返回两张表都满足条件的部分
关键词:INNER JOIN
格式:
显示的:SELECT 列名表 FROM 表名1 [INNER] JOIN
表名2 ON 条件表达式
隐式的:SELECT 列名表 FROM 表名1 ,表名2 WHERE 条件表达式
说明:
(1)列名表中的列名可以出自后面的两个表,但如果两个表中有同名列,应在列名前标明出处,格式为:表名.列名
(2)若连接的两个表名字太长,可以为它们起个别名。 格式为:表名 AS 别名
(3)INNER是默认方式,可以省略
1.1 显式的和隐式的
内连接有两种,显式的和隐式的
1.1.1 显示的
显示的内连接,一般称为内连接,有inner join
,形成的中间表为两个表经过on条件
过滤后的笛卡尔积。
select o.id,o.order_number,c.id,c.name from customers c inner join orders o on c.id=o.customer_id;
1.1.2 隐式的
隐式的内连接,没有inner join
,形成的中间表为两个表的笛卡尔积。
select o.id,o.order_number,c.id,c.name from customers c, orders o wherec.id=o.customer_id;
2. 外连接
外连不但返回符合连接和查询条件的数据行,还返回不符合条件的一些行。
外连接分三类:左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。
outer 关键字是可省略的。
2.1 左(外)连接 Left outer join
定义:在内连接的基础上,还包含左表中所有不符合条件的数据行
,并在其中的右表列填写NULL
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,排除在右表 (table_name2) 中没有匹配的行。
语法结构:
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
例:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集:
过程分析:
从左边表逐条和右边进行查找,如果没有匹配到,仍然把左侧的放入结果,此时右侧表字段用null代替
2.2 右(外)连接 right outer join
定义:在内连接的基础上,还包含右表中所有不符合条件的数据行,并在其中的左表列填写NULL
关键字:RIGHT JOIN
即整个黄色区域:
从右边表逐条和右边进行查找,如果没有匹配到,仍然把右侧的放入结果,此时左侧表字段用null代替
语法结构:
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.name = TableB.name
例:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行,即使在左表 (Persons) 中没有匹配的行。
2.3 全外连接 Full outer join
定义:在内连接的基础上,还包含两个表中所有不符合条件的数据行,并在其中的左表、和右表列填写NULL
关键字:FULL JOIN
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
例:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配,或者如果 “Orders” 中的行在表 “Persons” 中没有匹配,这些行同样会列出。
3. 交叉连接(迪卡尔积)
交叉连接:返回左表中的所有行,左表中的每一行与右表中的所有行组合
。交叉联接也称作笛卡尔积
3.1 笛卡尔积
首先,先简单解释一下笛卡尔积:笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
举例:
现在,我们有两个集合A和B。
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式:
A×B = {(0,2),(1,2),(0,3),(1,3),(0,4),(1,4)};
B×A = {(2,0),(2,1),(3,0),(3,1),(4,0),(4,1)};
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论:
1,两个集合相乘,不满足交换率,既 A×B ≠ B×A
;
2,A集合和B集合相乘,包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
3.2 交叉连接实例
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into `student` (`id`, `name`) values('1','zhangsan');
insert into `student` (`id`, `name`) values('2','lisi');
CREATE TABLE `course` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into `course` (`id`, `name`) values('1','语文');
insert into `course` (`id`, `name`) values('2','英语');
交叉连接分为隐式和显示:
- 隐式的交叉连接,没有CROSS JOIN。
SELECT s.name ,c.name FROM student AS s,course AS c
- 显式的交叉连接,使用CROSS JOIN。
SELECT s.name ,c.name FROM student AS s CROSS JOIN course AS c
不管显示还是隐式的,其结果一致:
3.3 交叉连接的作用
作用1:作为笛卡尔积显示,正如3.2 交叉连接实例那样
作用2:交叉连接可以作为连接查询的临时表
,再此基础上,如果有where 条件,再进行where过滤
临时表涉及到性能问题,先介绍下sql中主要关键字的执行顺序:
from
on
join
where
group by
having
select
distinct
union
order by
我们看到:
-
on
(on条件,可以进行过滤操作)的执行顺序是在join(连接操作)前面
的 -
where
(where条件,可以进行过滤操作)的执行顺序是在join后面
的
sql查询的基本原理:我们这里主要考虑第二种情况
-
第一、 单表查询:根据where条件过滤表中的记录,形成中间表(这个中间表对用户是不可见的);然后根据select的选择列选择相应的列进行返回最终结果。
-
第二、 两表连接查询:对两表求积(笛卡尔积)并用on条件和连接类型进行过滤形成中间表;然后根据where条件过滤中间表的记录,并根据select指定的列返回查询结果。
-
第三、 多表连接查询:先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据where条件过滤中间表的记录,并根据select指定的列返回查询结果。
理解sql查询的过程是进行sql优化的理论依据。
第三种其实是第二种的特殊情况,逐个进行2表连接,直至全部连接
第二种情况牵扯到on后面的条件(on条件)和where条件的区别:
相同点:on条件和where条件都是进行过滤的
不同点:
- on条件:是过滤两个链接表笛卡尔积形成中间表T1的约束条件。
- where条件:
在有on条件的select语句中是过滤中间表T1的约束条件(在中间表T1的基础上再进行过滤)。
在没有on的单表查询中,是限制物理表或者中间查询结果返回记录的约束。(和本章节无关)
由以上可知:
如果两张表的数据量都比较大的话,直接进行连接查询时(不带on条件),那样交叉连接生成的临时表就会占用很大的内存空间,这显然是不合理的。所以,我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx
的语法(带on条件),ON语句的执行是在JOIN语句之前的,也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合ON语句后面的条件,再决定是否JOIN。
因此,有一个显而易见的SQL优化的方案是,当两张表的数据量比较大,又需要连接查询时,应该使用 FROM table1 JOIN table2 ON xxx
的语法,避免使用 FROM table1,table2 WHERE xxx
的语法,因为后者会在内存中先生成一张数据量比较大的笛卡尔积表,增加了内存的开销。
on过滤过程:
where过滤过程:
参考:
《SQL LEFT JOIN 关键字》
《关系数据库内连接、左连接、右连接图示说明》
《Sql 中内连接、外连接、全连接、交叉连接的区别》交叉连接、笛卡尔积