Oracle-JOINS

CROSS JOIN(交叉连接)

CROSS JOIN在不使用显式连接子句的两个表之间执行连接，并产生两个表的笛卡尔积。笛卡尔积表示一个表的每一行与连接中的第二个表的每一行组合。例如，如果TABLEA有20行，TABLEB有20行，结果将是20 * 20 = 400个输出行。

例：

1 2	SELECT * FROM TABLEA CROSS JOIN TABLEB;

也可以写成：

1 2	SELECT * FROM TABLEA, TABLEB;

下面是两个表之间的SQL中的cross join的示例：

Sample Table: TABLEA

+-------+---------+
| VALUE |   NAME  |
+-------+---------+
|   1   |   ONE   |
|   2   |   TWO   |
+-------+---------+

现在，如果执行查询：

1 2	SELECT * FROM TABLEA CROSS JOIN TABLEB;

输出：

+-------+--------+-------+--------+
| VALUE |  NAME  | VALUE |  NAME  |
+-------+--------+-------+--------+
|   1   |   ONE  |   3   |  THREE |
|   1   |   ONE  |   4   |  FOUR  |
|   2   |   TWO  |   3   |  THREE |
|   2   |   TWO  |   4   |  FOUR  |
+-------+--------+-------+--------+

LEFT OUTER JOIN(左外连接)

LEFT OUTER JOIN在需要显式连接子句的两个表之间执行连接，但不会从第一个表中排除不匹配的行。

例：

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT  
      ON EMP.DEPTNO = DEPT.DEPTNO;

即使ANSI语法是推荐的方法，它很可能会经常遇到传统语法。在条件内使用(+)确定等式的哪一侧被认为是外侧。

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.EMP, 
      SCOTT.DEPT  
WHERE
      EMP.DEPTNO = DEPT.DEPTNO(+);

下面是两个表之间的Left Outer Join的示例：

Sample Table: EMPLOYEE

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     A     |    2    |
|     B     |    1    |
|     C     |    3    |
|     D     |    2    |
|     E     |    1    |
|     F     |    1    |
|     G     |    4    |
|     H     |    4    |
+-----------+---------+

Sample Table: DEPT

+---------+--------------+
|  DEPTNO |   DEPTNAME   |
+---------+--------------+
|    1    |  ACCOUNTING  |
|    2    |    FINANCE   |
|    5    |   MARKETING  |
|    6    |      HR      |
+---------+--------------+

现在，如果执行查询：

SELECT
      *
FROM
      EMPLOYEE LEFT OUTER JOIN DEPT
      ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;

输出：

+-----------+---------+---------+--------------+
|    NAME   |  DEPTNO |  DEPTNO |   DEPTNAME   |
+-----------+---------+---------+--------------+
|     F     |    1    |    1    |  ACCOUNTING  |
|     E     |    1    |    1    |  ACCOUNTING  |
|     B     |    1    |    1    |  ACCOUNTING  |
|     D     |    2    |    2    |    FINANCE   |
|     A     |    2    |    2    |    FINANCE   |
|     C     |    3    |         |              |
|     H     |    4    |         |              |
|     G     |    4    |         |              |
+-----------+---------+---------+--------------+

ANTIJOIN(反连接)

antijoin从谓词的左侧返回行，在谓词的右侧没有对应的行。它返回不匹配（NOT IN）右侧子查询的行。

SELECT * FROM employees 
   WHERE department_id NOT IN 
   (SELECT department_id FROM departments 
       WHERE location_id = 1700)
   ORDER BY last_name;

下面是两个表之间的反连接示例：

Sample Table: EMPLOYEE

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     A     |    2    |
|     B     |    1    |
|     C     |    3    |
|     D     |    2    |
|     E     |    1    |
|     F     |    1    |
|     G     |    4    |
|     H     |    4    |
+-----------+---------+

Sample Table: DEPT

+---------+--------------+
|  DEPTNO |   DEPTNAME   |
+---------+--------------+
|    1    |  ACCOUNTING  |
|    2    |    FINANCE   |
|    5    |   MARKETING  |
|    6    |      HR      |
+---------+--------------+

现在，如果执行查询：

SELECT
      *
FROM
      EMPLOYEE WHERE DEPTNO NOT IN
      (SELECT DEPTNO FROM DEPT);

输出：

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     C     |    3    |
|     H     |    4    |
|     G     |    4    |
+-----------+---------+

RIGHT OUTER JOIN(右外部接合)

RIGHT OUTER JOIN在需要显式连接子句的两个表之间执行联接，但不会从第二个表中排除不匹配的行。

例：

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT  
      ON EMP.DEPTNO = DEPT.DEPTNO;

由于包括SCOTT.DEPT的不匹配行，但不包括SCOTT.EMP的不匹配行，上述等价于使用LEFT OUTER JOIN的以下语句。

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP  
      ON DEPT.DEPTNO = EMP.DEPTNO;

下面是两个表之间的Right Outer Join的示例：

Sample Table: EMPLOYEE

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     A     |    2    |
|     B     |    1    |
|     C     |    3    |
|     D     |    2    |
|     E     |    1    |
|     F     |    1    |
|     G     |    4    |
|     H     |    4    |
+-----------+---------+

Sample Table: DEPT

+---------+--------------+
|  DEPTNO |   DEPTNAME   |
+---------+--------------+
|    1    |  ACCOUNTING  |
|    2    |    FINANCE   |
|    5    |   MARKETING  |
|    6    |      HR      |
+---------+--------------+

现在，如果执行查询：

SELECT
      *
FROM
      EMPLOYEE RIGHT OUTER JOIN DEPT
      ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;

输出：

+-----------+---------+---------+--------------+
|    NAME   |  DEPTNO |  DEPTNO |   DEPTNAME   |
+-----------+---------+---------+--------------+
|     A     |    2    |    2    |    FINANCE   |
|     B     |    1    |    1    |  ACCOUNTING  |
|     D     |    2    |    2    |    FINANCE   |
|     E     |    1    |    1    |  ACCOUNTING  |
|     F     |    1    |    1    |  ACCOUNTING  |
|           |         |    5    |   MARKETING  |
|           |         |    6    |      HR      |
+-----------+---------+---------+--------------+

FULL OUTER JOIN(完全外部联接)

FULL OUTER JOIN在需要显式连接子句的两个表之间执行连接，但不排除任一表中的不匹配行。换句话说，它返回每个表中的所有行。

例：

SELECT                                     
      *                                    
FROM                                       
      EMPLOYEE FULL OUTER JOIN DEPT 
      ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;

下面是两个表之间的完全外部联接的示例：

Sample Table: EMPLOYEE

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     A     |    2    |
|     B     |    1    |
|     C     |    3    |
|     D     |    2    |
|     E     |    1    |
|     F     |    1    |
|     G     |    4    |
|     H     |    4    |
+-----------+---------+

Sample Table: DEPT

+---------+--------------+
|  DEPTNO |   DEPTNAME   |
+---------+--------------+
|    1    |  ACCOUNTING  |
|    2    |    FINANCE   |
|    5    |   MARKETING  |
|    6    |      HR      |
+---------+--------------+

现在，如果执行查询：

SELECT
      *
FROM
      EMPLOYEE FULL OUTER JOIN DEPT
      ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;

输出：

+-----------+---------+---------+--------------+
|    NAME   |  DEPTNO |  DEPTNO |   DEPTNAME   |
+-----------+---------+---------+--------------+
|     A     |    2    |    2    |    FINANCE   |
|     B     |    1    |    1    |  ACCOUNTING  |
|     C     |    3    |         |              |
|     D     |    2    |    2    |    FINANCE   |
|     E     |    1    |    1    |  ACCOUNTING  |
|     F     |    1    |    1    |  ACCOUNTING  |
|     G     |    4    |         |              |
|     H     |    4    |         |              |
|           |         |    6    |      HR      |
|           |         |    5    |   MARKETING  |
+-----------+---------+---------+--------------+

这里，不匹配的列保持为NULL。

INNER JOIN(内部联接)

INNER JOIN是一个JOIN操作，允许你指定显式连接子句。

句法：

TableExpression [ INNER ] JOIN TableExpression { ON booleanExpression | USING clause }

可以通过使用布尔表达式指定ON来指定连接子句。

ON子句中的表达式的范围包括当前表和外部查询块中到当前SELECT的任何表。在以下示例中，ON子句引用当前表：

-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and 
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
 FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
 ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Join the EMPLOYEE and DEPARTMENT tables, 
-- select the employee number (EMPNO),  
-- employee surname (LASTNAME), 
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table) 
-- and department name (DEPTNAME) 
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 
 FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT 
 ON WORKDEPT = DEPTNO 
 AND YEAR(BIRTHDATE) < 1930
-- Another example of "generating" new data values, 
-- using a query which selects from a VALUES clause (which is an 
-- alternate form of a fullselect). 
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1
-- This results in:
-- C1         |C2         |C1         |2
-- -----------------------------------------------
-- 3          |4          |3          |2
-- 1          |5          |1          |2 
-- List every department with the employee number and 
-- last name of the manager
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT INNER JOIN EMPLOYEE
ON MGRNO = EMPNO
-- List every employee number and last name 
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME    
FROM EMPLOYEE E INNER JOIN    
DEPARTMENT INNER JOIN EMPLOYEE M 
    ON MGRNO = M.EMPNO
    ON E.WORKDEPT = DEPTNO

JOIN

JOIN操作在两个表之间执行联接，排除第一个表中的任何不匹配的行。从Oracle 9i向前，JOIN在功能上等同于INNER JOIN。此操作需要一个显式连接子句，而不是CROSS JOIN和NATURAL JOIN运算符。

例：

select t1.*,
       t2.DeptId
  from table_1 t1
  join table_2 t2 on t2.DeptNo = t1.DeptNo

Oracle文档：

NATURAL JOIN(自然加入)

NATURAL JOIN不需要显式连接条件; 它将基于连接表中具有相同名称的所有字段构建一个。

create table tab1(id number,  descr varchar2(100));
create table tab2(id number,  descr varchar2(100));
insert into tab1 values(1, 'one');
insert into tab1 values(2, 'two');
insert into tab1 values(3, 'three');
insert into tab2 values(1, 'ONE');
insert into tab2 values(3, 'three');

连接将在两个表共用的字段ID和DESCR上完成：

SQL> select *
  2  from tab1
  3        natural join
  4       tab2;
        ID DESCR
---------- ----------
         3 three

具有不同名称的列将不会在JOIN条件中使用：

SQL> select *
  2  from (select id as id, descr as descr1 from tab1)
  3       natural join
  4       (select id as id, descr as descr2 from tab2);
        ID DESCR1     DESCR2
---------- ---------- ----------
         1 one        ONE
         3 three      three

如果连接的表没有公共列，则将执行没有条件的JOIN：

SQL> select *
  2  from (select id as id1, descr as descr1 from tab1)
  3       natural join
  4       (select id as id2, descr as descr2 from tab2);
       ID1 DESCR1            ID2 DESCR2
---------- ---------- ---------- ----------
         1 one                 1 ONE
         2 two                 1 ONE
         3 three               1 ONE
         1 one                 3 three
         2 two                 3 three
         3 three               3 three

SEMI JOIN(半连接)

例如，可以使用semijoin 查询查找具有至少一个员工的工资超过2500的所有部门。

SELECT * FROM departments 
   WHERE EXISTS 
   (SELECT 1 FROM employees 
       WHERE departments.department_id = employees.department_id 
       AND employees.salary > 2500)
   ORDER BY department_name;

这比完整的联接替代方案更有效率，因为内部加入员工，然后给出一个where子句，详细说明工资必须大于2500可以多次返回相同的部门。说如果消防部门有n个员工都有工资3000，select * from departments，employees有必要加入的员工在id和我们的where子句将返回消防部门n次。