黑猴子的家：mysql 连接查询（全外连接和自连接）练习

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

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

2、查询哪个城市没有部门

SELECT city
FROM locations l
LEFT OUTER JOIN departments d
ON l.location_id =d.location_id
WHERE d.department_id IS NULL;

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

SELECT e.*,d.department_name,d.department_id
FROM employees e
RIGHT JOIN departments d ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('SAL','IT');

4、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees   Emp     manager    Mgr
kochhar     101     king       100

SELECT e.last_name employees,e.`employee_id` "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e
INNER JOIN employees m ON e.`manager_id`=m.employee_id
WHERE e.`last_name`='kochhar';