#自连接 #查询员工的名字、上级的名字 SELECT e.`last_name`,m.`last_name` FROM `employees` e INNER JOIN `employees` m ON e.`manager_id`=m.`employee_id`;
#查询姓名中包含字符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;
展现的是笛卡尔乘积
#左外 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的 但是没有员工的部门存在 所以最好用外连接
#左外 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');
同样的效果