【MySQL练习】多表查询练习（一）

表数据：https://www.cnblogs.com/zhishu/p/16452950.html

1.显示所有员工的姓名，部门号和部门名称。

所有员工，用left join。
left outer join和left join的效果是一样的。

#107条数据，有的员工没有部门
SELECT a.last_name,a.department_id,b.department_name
FROM employees a
LEFT JOIN departments b ON a.department_id = b.department_id;

#错误的，106条数据
SELECT a.last_name,a.department_id,b.department_name
FROM employees a,departments b
WHERE a.department_id = b.department_id;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT a.job_id,b.location_id
FROM employees a
JOIN departments b ON a.department_id = b.department_id
WHERE b.department_id = '90';

#不用join的方式
SELECT a.job_id,b.location_id
FROM employees a,departments b
WHERE a.department_id = b.department_id AND b.department_id = '90';

3.选择所有有奖金的员工的 last_name , department_name , location_id , city

所有员工，使用left join

SELECT a.last_name,b.department_name,b.location_id,c.city
FROM employees a
LEFT JOIN departments b ON a.department_id = b.department_id
LEFT JOIN locations c ON b.location_id = c.location_id
WHERE a.commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

SELECT a.last_name,a.job_id,a.department_id,b.department_name
FROM employees a
JOIN departments b ON a.department_id = b.department_id
JOIN locations c ON b.location_id = c.location_id
WHERE c.city = 'Toronto';

#或
SELECT a.last_name,a.job_id,b.department_id,b.department_name
FROM employees a,departments b,locations c
WHERE a.department_id = b.department_id AND b.location_id = c.location_id;

5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT b.department_name,CONCAT(c.state_province,c.street_address) AS address,a.last_name,d.job_title,a.salary
FROM employees a
JOIN departments b ON a.department_id = b.department_id
JOIN locations c ON b.location_id = c.location_id
JOIN jobs d ON a.job_id = d.job_id
WHERE b.department_name = 'Executive';

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

employees    Emp#    manager    Mgr#
kochhar           101    king             100

SELECT emp.last_name AS "employees",emp.employee_id AS "Emp",mgr.last_name AS "manager",mgr.employee_id AS "Mgr"
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id;

7.查询哪些部门没有员工

b表的哪个字段为空都行，一般写b.department_id IS NULL

#第一种方式
SELECT a.department_id,a.department_name
FROM departments a
LEFT JOIN employees b ON a.department_id = b.department_id
WHERE b.employee_id IS NULL;

#第二种方式：查询不存在相等的，即为没有部门的员工
SELECT a.department_id,a.department_name
FROM departments a
WHERE NOT EXISTS(
	SELECT * 
	FROM employees b
	WHERE a.department_id =b.department_id
);

8. 查询哪个城市没有部门

SELECT a.city
FROM locations a
LEFT JOIN departments b ON a.location_id = b.location_id
WHERE b.department_id IS NULL;

9. 查询部门名为 Sales 或 IT 的员工信息

SELECT a.employee_id,a.last_name,b.department_name
FROM employees a
JOIN departments b ON a.department_id = b.department_id
WHERE b.department_name = 'Sales' OR b.department_name = 'IT';

#或
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');

原文地址：http://www.cnblogs.com/zhishu/p/16799834.html