1z0-071 Oracle Database 12c SQL 第23题 JOIN ON

Q23. View the exhibit and examine the structure of the EMPLOYEES table.

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id = 100;
B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100;
C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id = 100;
D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100

Correct Answer: B

JOIN等价于INNER JOIN，表m看作是管理员表，表e是员工表，m和e的关系是1对多，根据提纲，第一列显示管理员的姓，第二列显示员工的姓。如果一个员工是管理员，那么他的员工ID（employee_id）就是其下属的管理员ID（manager_id），管理员表和员工表的关联条件为m.employee_id = e.manager_id，筛选条件为e.manager_id = 100。

那么，数据的流向为：

1、根据e.manager_id = 100生成一个新员工表，里边员工的管理员ID同为100；

2、用管理员表m的员工ID（employee_id）与新员工表的管理员ID（manager_id）进行等值关联；

3、显示管理员表m中的姓（last_name）和新员工表中的姓。

sql的left join 、right join 、inner join之间的区别
1、left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
2、right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
3、inner join(等值连接) 只返回两个表中联结字段相等的行