多表连接--案例讲解

#多表连接，案例讲解
#1.查询部门编号>3的女神的男朋友信息，如果有则列出详情，如果没有，用null填充
#外连接
SELECT b.`name`,b.`id`, bo.*
FROM  `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;
#2.查询哪个城市没有部门
#右外
SELECT city,d.*
FROM  `departments` d  
RIGHT OUTER JOIN   `locations` l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL; 


#3查询部门名为SAL或IT的员工
#左外，部门名一定是有的，但是员工满足条件的就不一定存在了，所以把部门表当作主表
SELECT  e.*,d.`department_name`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE `department_name` IN('SAL','IT');
#查出来39行

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

#查出来41行