连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。
- 连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
- 通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。
- 例如:想在查询emp表的同时,把每个员工的所在部门信息显示出来,那么就需要使用主外键来去除无用信息了。
(1)连接查询,产生笛卡尔积:
SELECT * FROM emp,dept;
运行结果:
(2)使用主外键关系做为条件来去除无用信息【通过关联关系去除笛卡尔积】
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ;
运行结果:
内连接
- 上面的连接语句就是内连接,但它不是SQL中标准的查询方式,可以理解为方言!SQL标准的内连接为:
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
- INNER可以省略,MySQL默认的连接方式就是内连接
- 不使用WHERE,而是使用ON
- 内连接的特点:查询结果必须满足条件。
外连接(左连接、右连接)
外连接的特点:查询出的结果存在不满足条件的可能。
(1) 左连接
左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL。
【示例】
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
【运行结果】
【分析】:其中,在emp表中“张三”这条记录,部门编号为50,而dept表中不存在部门编号为50的记录,所以“张三”这条记录,不能满足e.deptno=d.deptno这条件。但在左连接中,因为emp表是左表,所以左表中的记录都会查询出来,即“张三”这条记录也会查出,但相应的右表部分显示NULL。
(2)右连接
右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。
【示例】
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
【运行结果】
【分析】:在dept表中的40部门并不存在员工,但在右连接中,如果dept表为右表,那么还是会查出40部门,但相应的员工信息为NULL。
连接查询心得:
- 连接不限与两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
- 两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件。
自然连接
- 大家也都知道,连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:两张连接的表中名称和类型完成一致的列作为条件。
- 例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!
- 当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!
(1)内连接
SELECT * FROM emp NATURAL JOIN dept;
(2)左连接
SELECT * FROM emp NATURAL LEFT JOIN dept;
(3)右连接
SELECT * FROM emp NATURAL RIGHT JOIN dept;