Oracle--- 多表连接 （三）

-- 等值连接
-- SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.department_name
-- FROM EMPLOYEES,DEPARTMENTS
-- WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
--给表起别名
-- SELECT e.EMPLOYEE_ID,e.DEPARTMENT_ID,d.department_name
-- FROM EMPLOYEES e,DEPARTMENTS d
-- WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 三个表相连
-- SELECT e.EMPLOYEE_ID,e.DEPARTMENT_ID,d.department_name,l.CITY
-- FROM EMPLOYEES e,DEPARTMENTS d ,LOCATIONS l
-- WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID =l.LOCATION_ID

--连接n 个表至少需要n-1个关系

--SELECT *  FROM JOB_GRADES
--  没有Where条件（过滤条件），就是笛卡儿积的
-- SELECT EMPLOYEE_ID,LAST_NAME,SALARY,GRADE_LEVEL
-- FROM EMPLOYEES e,JOB_GRADES j
-- WHERE e.SALARY BETWEEN j.LOWEST_SAL AND j.HIGHEST_SAL

--外连接 
--1)内链接 ： （等值&不等值）
-- SELECT e.last_name,e.DEPARTMENT_id, d.DEPARTMENT_name
-- FROM EMPLOYEES e, DEPARTMENTS d
-- WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 内链接:合并具有同一列的两个以上的表的行
-- 结果集中不包含一个表与另一个表不匹配的行(king 滤掉 107行数据变为106行 )

-- 外连接 ： 两个表在连接过程中除了返回满足连接条件的行还返回左或右
--外连接。 没有匹配的行时，结果表中相应的列为空（NULL）、外连接的where子句
--条件类似与内链接，但连接条件中么有匹配行的表的列后面要加外连接运算，
--即用圆括号起来的加号（+）
--左外连接
-- SELECT e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
-- FROM EMPLOYEES e, DEPARTMENTS d
-- WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)[显示左表中不满足条件的]
--右外连接
-- SELECT e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
-- FROM EMPLOYEES e, DEPARTMENTS d
-- WHERE e.DEPARTMENT_ID(+) = d.DEPARTMENT_ID

--SQL 1999语法 using()要求2个表中的字段数据类型 名称一样
-- SELECT EMPLOYEE_id,d.DEPARTMENT_ID, DEPARTMENT_NAME
-- FROM EMPLOYEES e join DEPARTMENTS d
-- --using(DEPARTMENT_ID)
-- ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

--实现三个表的连接 join on 
-- SELECT EMPLOYEE_id,d.DEPARTMENT_ID, d.DEPARTMENT_NAME,city
-- FROM EMPLOYEES e join DEPARTMENTS d
-- --using(DEPARTMENT_ID)
-- ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- join LOCATIONS l
-- ON d.LOCATION_id = l.location_id

--左链接
-- SELECT EMPLOYEE_id,d.DEPARTMENT_ID, d.DEPARTMENT_NAME
-- FROM EMPLOYEES e LEFT JOIN  DEPARTMENTS d
-- ON e.DEPARTMENT_ID = d.DEPARTMENT_ID


--右连接
-- SELECT EMPLOYEE_id,d.DEPARTMENT_ID, d.DEPARTMENT_NAME
-- FROM EMPLOYEES e RIGHT JOIN  DEPARTMENTS d
-- ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

--左右都出来
-- SELECT EMPLOYEE_id,d.DEPARTMENT_ID, d.DEPARTMENT_NAME
-- FROM EMPLOYEES e full JOIN  DEPARTMENTS d
-- ON e.DEPARTMENT_ID = d.DEPARTMENT_ID


--自连接(在同一张表)
--查询公司员工'chen'的老板manager的信息
-- SELECT last_name,manager_id
-- FROM EMPLOYEES
-- WHERE lower(last_name) = 'chen'
-- 
-- SELECT EMP.LAST_NAME,manager.LAST_NAME,manager.SALARY,manager.EMAIL
-- FROM EMPLOYEES EMP,EMPLOYEES manager
-- WHERE emp.MANAGER_ID = MANAGER.EMPLOYEE_ID AND LOWER(EMP.LAST_NAME ) ='chen'