自然连接(NATURAL JOIN)
它会帮你自动查询两张连接表中所有相同的字段,然后进行等值连接。
在SQL92标准中:
SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id';
AND e.'manager_id'=d.'manager_id';
在SQL99中可写成
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
USING
USING制定了具体的相同的字段名称,你需要在USING的括号中填入要指定的同名字段。同时使用JOIN...USING可以简化JOIN ON的等值连接。
SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id';
可写成
SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
USING(department_id);
多表查询课后练习
显示所有员工的姓名,部门号和部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employee e LEFT OUTER JOIN departments d
ON e.'department_id' =d.'department_id';
查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id'
WHERE d.'department_id'=90;
查询所有有奖金的员工的last_name,department_name,location_id,city
SELECT last_name,department_name,location_id,city
FROM employees e LEFT JOIN departments d
ON e.'department_id'=d.'department_id'
LEFT JOIN locations l
ON d.'location_is'=l.'location_id'
WHERE e.'commission_pct' IS NOT NULL;
选择city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e,departments d,locations l
WHERE e.'department_id'=d.'department_id'
AND d.'location_id'=l.'location_id'
AND l.'city'='Toronto';
#sql92语法
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e,departments d, locations l
WHERE e.'department_id'=d.'department_id'
AND d.'location_id'=l.'location_id'
AND l.'city'='Toronto';
查询员工所在的部门名称,部门地址,姓名,工作,工资,其中员工所在部门的部门名称为“Execntive”
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM departments d LEFT JOIN employees e
ON e.'department_id'=d.'department_id'
LEFT JOIN locations l
ON d.'location_id'=l.'location_id'
WHERE d.'department_name'='Executive';
选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
SELECT emp.last_name "employees", emp.employee_id"Emp#", mgr.last_name"manager", mgr.employee_id"Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id=mgr.employee_id;
查询哪些部门没有员工
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.'department_id'=e.'department_id'
WHERE e.'department_id' IS NULL;
#本题也可以使用子查询
查询哪个城市没有部门
SELECT location_id, city
FROM locations JOIN departments d
ON l.'location_id'=d.'location_id'
WHERE d.'location_id' IS NULL;
查询部门名为Sales或IT的员工信息
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id'
WHERE d.'department_name' IN ('Sales', 'IT');