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
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(等值连接) 只返回两个表中联结字段相等的行