MySQL-sql99-自连接、左（右）外连接、全外连接、交叉连接

自连接

查询员工的名字、上级的名字

#自连接
#查询员工的名字、上级的名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id`=m.`employee_id`;

还可以继续加筛选条件

查询姓名中包含字符k的员工的名字、上级的名字

#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

外连接

引入：查询男朋友不在男神表的女神名

SELECT b.`name`,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`

所有信息都查出来了

加上筛选条件

# 引入：查询男朋友不在男神表的女神名
SELECT b.`name`,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NOT NULL;

选择bo.id 作为筛选条件 是因为

主键不为空

若 把id=3的userCP=NULL

UPDATE boys SET userCP=NULL WHERE id=3;

则本身这个字段就为空 不能作为筛选条件 所以一般选择主键

上面的代码为左外连接

要实现右外连接 见下图

SELECT b.`name`,bo.*
FROM `boys` bo
RIGHT OUTER JOIN `beauty` b 
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NOT NULL;

案例：哪个部门没有员工

部门是主表

#左外
SELECT d.*,e.`department_id`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

#右外

SELECT d.*,e.`department_id`
FROM `employees` e
RIGHT OUTER JOIN `departments` d
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

全外连接

交叉连接

SELECT b.* ,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;

展现的是笛卡尔乘积

总结连接查询

案例讲解

案例：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用NULL填充

#左外
SELECT b.`id`,b.`name`,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.id>3;

案例：查询哪个城市没有部门

#左外
SELECT l.`city`,d.`department_name`
FROM `locations` l
LEFT OUTER JOIN `departments` d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;

案例：查询部门名为SAL或IT的员工信息

这里因为有可能出现 部门名为SAL或IT的 但是没有员工的部门存在 所以最好用外连接

#左外
SELECT d.`department_name`,e.*
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name`='SAL' OR d.`department_name`='IT';

或者

#左外
SELECT d.`department_name`,e.*
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name`IN('SAL','IT');

同样的效果