多表查询

表关系简介

一、语法

SELECT 字段列表  FROM TABLE1 [CROSS JOIN TABLE2 ] | [NATURAL JOIN TABLE2 ] |  [JOIN TABLE2 USING (字段名) ] |  [JOIN TABLE2 ON (TABLE.COLUMN_NAME = TABLE2.COLUMN_NAME) ] |[(LEFT | RIGHT | FULL OUT) JOIN TABLE2 ON (TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME) ];

二、自然连接

1. USING
   如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列(参照列)名。
   不允许在参照列上使用表名或者别名作为前缀
   

   --99语法SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME  FROM EMP  JOIN DEPT USING (DEPTNO); --92语法SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME  FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

2. ON
   如果要参照非同名的列进行等值连接,或想设置任意的连接条件,可以使用On子句
   

   SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME  FROM EMP  JOIN DEPT    ON (EMP.DEPTNO = DEPT.DEPTNO);

三、自连接

自己和自己做链接

--99SELECT WORKER.LAST_NAME EMP, MANAGER.LAST_NAME MGR  FROM EMPLOYEES WORKER  JOIN EMPLOYEES MANAGER    ON (WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID);--92SELECT WORKER.LAST_NAME EMP, MANAGER.LAST_NAME MGR  FROM EMPLOYEES WORKER, EMPLOYEES MANAGER WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID;

四、非等值连接

--99SELECT E.LAST_NAME, E.SALARY, J.GRADE_LEVEL  FROM EMPLOYEES E  JOIN JOB_GRADES J    ON E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;--92SELECT E.LAST_NAME, E.SALARY, J.GRADE_LEVEL  FROM EMPLOYEES E, JOB_GRADES J WHERE E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

五、INNER|OUTER 连接

1. INNER 连接
   两个表连接，只返回匹配的行，被称为内连接。
   

   --查询所有员工以及其部门名字--99SELECT LAST_NAME, DEPARTMENT_NAME  FROM EMPLOYEES E INNER JOIN DEPARTMENTS D    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;​--92SELECT LAST_NAME, DEPARTMENT_NAME  FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

2. LEFT OUTER | RIGHT OUTER 连接
   两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。
   

   --哪些部门没有员工，看出部门名字--99SELECT DEPARTMENT_NAME  FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID WHERE LAST_NAME IS NULL;​--92SELECT DEPARTMENT_NAME  FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID   AND LAST_NAME IS NULL;

3. FULL OUTER 连接
   两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为全外联接。
   

   --99SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME  FROM EMPLOYEES E  FULL OUTER JOIN DEPARTMENTS D    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;​--92SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME  FROM EMPLOYEES E, DEPARTMENTS D    ON E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID(+);

六、笛卡尔积

笛卡尔集会在下面条件下产生:

• 连接条件被遗漏
• 连接条件不正确
• 所有表中的所有行互相连接

--99SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME  FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;​--92SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME  FROM EMPLOYEES E, DEPARTMENTS D;