查找所有已经分配部门的员工的last_name和first_name以及dept_no
描述
有一个员工表,employees简况如下:
有一个部门表,dept_emp简况如下:
建表语句如下:
CREATE TABLE employees
(emp_no
int(11) NOT NULL,birth_date
date NOT NULL,first_name
varchar(14) NOT NULL,last_name
varchar(16) NOT NULL,gender
char(1) NOT NULL,hire_date
date NOT NULL,
PRIMARY KEY (emp_no
));
CREATE TABLE dept_emp
(emp_no
int(11) NOT NULL,dept_no
char(4) NOT NULL,from_date
date NOT NULL,to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
请你查找所有已经分配部门的员工的last_name和first_name以及dept_no,未分配的部门的员工不显示,以上例子如下:
写法一:
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e,dept_emp d
WHERE e.emp_no = d.emp_no
写法二:
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e INNER JOIN dept_emp d
ON e.emp_no = d.emp_no
为什么要用INNER JOIN 关联两张表?
employees表中的dept_no有可能为NULL;但dep_emp表中的dept_no一定不为NULL,所以使用内部联结,去除dept_no为NULL的employees。题目中要求的答案就是要已经分配的部门员工的信息,所以dep_no 为NULL 的员工不需要显示
联接时,如果需要把dept_no=NULL的数据也列出,要使用外部联结 LEFT/RIGHT OUTER JOIN ... ON ...
写法三:
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e JOIN dept_emp d
ON e.emp_no = d.emp_no
如果题目改成:
查找所有员工的last_name和first_name以及对应部门编号dept_no
也就是说就算没有分配部门的员工(dept_emp.dept_no = NULL) 都是答案,那么此时就用外部连接(LEFT/RIGHT JOIN)
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e LEFT JOIN dept_emp d
ON e.emp_no = d.emp_no