二、外连接

应用场景：用于查询一个表中有，而另一个表没有的记录

特点：

1. 外连接的查询结果为‘主’表中的所有记录

   如果‘从’表中有和它匹配的，则显示匹配的值
   （图中示意：beauly为主表，boys为从表。）
   
   如果从表中没有和它匹配的，则显示null
   
   外连接查询结果=内连接结果+主表中有而从表没有的记录

2. 左外连接，lefe，join左边的是主表

   右外连接，right，join右边的是主表

3. 左外和右外交换两个表的顺序，可以实现同样的效果

语法

SELECT 查询列表
FROM 主表 LEFT JOIN 从表 ON 连接条件

案例

复习：查询男朋友 在 男生表的女生名

SELECT 
  NAME,
  boyName 
FROM
  beauty 
  INNER JOIN boys 
    ON beauty.`boyfriend_id` = boys.`id` ;

引入：查询男朋友 不在 男生表的女生名 左外连接 右外连接 主从调换

#左外连接
SELECT 
  be.`name`,
  bo.* 
FROM
  beauty AS be 
  LEFT OUTER JOIN boys AS bo 
    ON be.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;

#右外连接
SELECT 
  be.`name`,
  bo.* 
FROM
  boys AS bo 
  RIGHT OUTER JOIN beauty AS be 
    ON be.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;

#主从调换
SELECT 
  be.*,
  bo.* 
FROM
  boys AS bo 
  LEFT OUTER JOIN beauty be 
    ON be.`boyfriend_id` = bo.`id` 
WHERE be.`id` IS NULL ;

案例1：查询那个部门没有员工 左外 右外

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

#右外
SELECT 
  DISTINCT d.`department_name`
FROM
  `employees` AS e 
  RIGHT OUTER JOIN `departments` AS d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

全外连接 （这里不支持的）

SELECT 
  DISTINCT d.`department_name`
FROM
  `employees` AS e 
  RIGHT OUTER JOIN `departments` AS d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

交叉连接

这里的结果是笛卡尔乘积

SELECT 
  be.`name`,
  bo.`boyName` 
FROM
  beauty AS be  
  CROSS JOIN boys AS bo ;

#总结

功能：  sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离。

示意图：

总结

功能： sql99支持的较多

可读性：sql99实现连接条件和筛选条件的分离。

三道小题

一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT 
  bo.* 
FROM
  beauty AS be 
  LEFT OUTER JOIN boys AS bo 
    ON be.`boyfriend_id` = bo.`id`
    WHERE be.`id`>3 ;

二、查询那个城市没有部门

SELECT 
  depar.`department_name`,
  loc.`city` 
FROM
  `locations` AS loc 
  LEFT OUTER JOIN `departments` AS depar 
    ON depar.`location_id` = loc.`location_id`
    WHERE depar.`department_id` IS NULL;

三、查询部门名为SAL或IT的员工信息

SELECT 
  em.*,
  dep.`department_name` 
FROM
  departments AS dep 
  LEFT OUTER JOIN employees AS em 
    ON dep.`department_id` = em.`department_id` 
WHERE dep.`department_name` IN ('SAL', 'IT') ;