ORACLE 表连接总结

使用一个连接从多个表中查询数据 
SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 =table2.column2; 
:: 在WHERE 子句中写连接条件 
:: 当多个表中有相同的列名时，将表名作为列名的前缀 


注:为了连接 n 个表在一起，你最少需要 n-1 个连接条件。如果表中有一个连接主键，该规则可能不适用，其中可能有多行用来唯一地标识每一行。 


等值连接 
注：等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins) 

select .. 
from ... 
where employees.department_id=departments.department_id; 



使用表别名 
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, .location_id 
FROM employees e , departments d 
WHERE e.department_id = d.department_id; 

原则 
:: 表别名最多可以有 30 个字符，但短一些更好。 
:: 如果在 FROM 子句中表别名被用于指定的表，那么在整个 SELECT 语句中都要使用表别名。 
:: 表别名应该是有意义的。 
:: 表别名只对当前的 SELECT 语句有效。 



非等值连接 


select .. 
from .. 
where e.salary BETWEEN j.lowest_sal AND j.highest_sal 


注：其它条件，例如 <= 和 >= 可以被使用，但 BETWEEN 是最简单的。在使用 BETWEEN 时先指定最低值后指定最高值。 

解释 :: 
BETWEEN…AND…实际上被Oeacle服务器转换为一对AND 条件(a >= 最小值)and(a<=最大值) ， 

IN (…) 被 Oracle 服务器转换为一组 OR条件 (a= value1 OR a = value2 OR a = value3)。 

所以用 BETWEEN … AND … 、IN(…) 并没有性能上的提高；好处是逻辑上简单。 



外连接 

用外连接返回不直接匹配的记录 
如果一个行不满足连接条件，该行将不出现在查询结果中(等值连接) 


外连接语法 
:: 你可以用一个外连接查看那些不满足连接条件的行 
:: 外连接运算符是加号(+) 
SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column(+)=table2.column; 

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column = table2.column(+) 

  table2.column (+) 是外连接符号，它可以放在 WHERE 子句的条件的任一边，但不能两边都放。(跟着没有匹配行的表中列的名字放置外连接符号)。 

SELECT e.last_name, e.department_id, d.department_name 
FROM employees e,departments 
WHERE e.department_id(+) = d.department_id ; 

LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME 
Whalen 10 Administration 
Hartstein 20 Marketing 
110 Accounting


外连接约束 
:: 外连接操作符只能出现在表达式一侧—缺少信息的一侧。它从一个表中返回那些在另一个表中没有直接匹配的行。 
:: 包括一个外连接的条件不能用 IN 操作符或连接到另一个用 OR 操作符的条件。 


自连接 




用SQL 连接表: 1999 语法 

用一个连接从多个表中查询数据 

SELECT table1.column, table2.column 
FROM table1 
[CROSS JOIN table2] | 
[NATURAL JOIN table2] | 
[JOIN table2 USING (column_name)] | 
[JOIN table2 
  ON(table1.column_name= table2.column_name)] | 
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name= table2.column_name)]; 


table1.column 表示要从其中返回数据的表和列 
CROSS JOIN 从两个表中返回笛卡尔乘积。 
NATURAL JOIN 基于相同的列名连接两个表 
JOIN table 
USING column_name 执行一个基于列名的等值连接 
JOIN table ON 
table1.column_name 执行一个基于在 ON 子句中的条件的等值连接 
= table2.column_name 
LEFT/RIGHT/FULL OUTER 



创建交叉连接 
:: CROSS JOIN子句导致两个表的交叉乘积 
:: 该连接和两个表之间的笛卡尔乘积是一样的 

Select last_name,department_name 
From employees 
CROSS JOIN departments; 

等效于: 
SELECT last_name, department_name 
FROM employees, departments; 


创建自然连接 
:: NATURAL JOIN子句基于两个表之间有相同名字的所有列 
:: 它从两个表中选择在所有的匹配列中有相等值的行 
:: 如果有相同名字的列的数据类型不同，返回一个错误 

注：连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。 

这是在两个表中唯一名字相同的列。如果存在其它的公共列，连接会全部使用他们。/././ 


SQL> desc departments; 
名称                                                  是否为空? 类型 
----------------------------------------------------- -------- ----------------- 
DEPARTMENT_ID                                         NOT NULL NUMBER(4) 
DEPARTMENT_NAME                                       NOT NULL VARCHAR2(30) 
MANAGER_ID                                                     NUMBER(6) 
LOCATION_ID                                                    NUMBER(4) 

SQL> desc locations; 
名称                                                  是否为空? 类型 
----------------------------------------------------- -------- ----------------- 
LOCATION_ID                                           NOT NULL NUMBER(4) 
STREET_ADDRESS                                                 VARCHAR2(40) 
POSTAL_CODE                                                    VARCHAR2(12) 
CITY                                                  NOT NULL VARCHAR2(30) 
STATE_PROVINCE                                                 VARCHAR2(25) 
COUNTRY_ID                                                     CHAR(2) 


SELECT department_id, department_name,location_id, city 
FROM departments 
NATURAL JOIN locations ; 


自然连接也可以被写为等值连接： 
SELECT department_id, department_name, 
departments.location_id, city 
FROM departments, locations 
WHERE departments.location_id = locations.location_id; 


带 WHERE 子句的自然连接 
可以用 WHERE 子句实现在一个自然连接中添加约束。下面的例子限制部门号 department ID 等于 20 或 50 那些输出的行。 
SELECT department_id, department_name, 
location_id, city 
FROM departments 
NATURAL JOIN locations 
WHERE department_id IN (20, 50); 


用USING 子句创建连接 
:: 如果一些列有相同的名字，但数据类型不匹配，NATURAL JOIN子句能够用USING 子句修改以指定将被用于一个等值连接的列 
:: 当有多个列匹配时，用USING 子句匹配唯一的列 
:: 在引用列不要使用表名或者别名 
:: NATURAL JOIN 和USING子句是相互排斥的 


SELECT l.city, d.department_name 
FROM locations l JOIN departments d USING (location_id) 
WHERE d.location_id = 1400; 
ORA-25154: column part of USING clause cannot have qualifier 
该语句是无效的，因为 LOCATION_ID 在 WHERE 子句中被限制了 

同样的限制也用于 NATURAL 连接。因此，那些在两个表中有相同名字的列不能没有任何限定词。 

SELECT e.employee_id, e.last_name, d.location_id 
FROM employees e JOIN departments d 
USING (department_id) ; 

该例子也写成一个等值连接： 
SELECT employee_id, last_name, 
employees.department_id, location_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id; 


用ON 子句创建连接 
:: 对于自然连接的连接条件，基本上是带有相同名字的所有列的等值连接 
:: 为了指定任意条件，或者指定要连接的列，可以使用ON 子句 
:: 连接条件从另一个搜索条件中被分开 
:: ON 子句使得代码易懂 

ON 条件 
用 ON 子句指定一个连接条件。这让你从在 WHERE 子句中的查找或过滤条件中分离指定的连接条件。 


用ON 子句返回记录 
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id 
FROM employees e JOIN departments d 
ON (e.department_id = d.department_id); 


ON 子句也可以象下面一样被用于有不同名字的连接列： 
SELECT e.last_name emp, m.last_name mgr 
FROM employees e JOIN employees m 
ON (e.manager_id = m.employee_id); 


用ON 子句创建三向连接 
SELECT employee_id, city, department_name 
FROM employees e 
JOIN departments d 
ON d.department_id = e.department_id 
JOIN locations l 
ON d.location_id = l.location_id; 

这也可以被写为三个等值连接： 
SELECT employee_id, city, department_name 
FROM employees, departments, locations 
WHERE employees.department_id = departments.department_id 
AND departments.location_id = locations.location_id; 


下面的例子显示也可以用 USING 子句完成同样的连接： 
SELECT e.employee_id, l.city, d.department_name 
FROM employees e 
JOIN departments d 
USING (department_id) 
JOIN locations l 
USING (location_id); 



默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。 


左外连接 

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。 
SQL> select e.last_name,e.department_id,d.department_name 
  2  from employees e 
  3  left outer join departments d 
  4  on( e.department_id=d.department_id); 

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- ------------------- 
Urman                               100 Finance 
Sciarra                             100 Finance 
Chen                                100 Finance 
Faviet                              100 Finance 
Greenberg                           100 Finance 
Gietz                               110 Accounting 
Higgins                             110 Accounting 
Grant 

上述语句可以写成如下:(结果如上) 

SELECT e.last_name, e.department_id, d.department_name 
FROM employees e, departments d 
WHERE d.department_id (+) = e.department_id; (+)出现在少的那边 





右外连接 

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。 
SQL> SELECT e.last_name, e.department_id, d.department_name 
  2   from employees e 
  3  RIGHT OUTER JOIN departments d 
  4   ON (e.department_id = d.department_id); 

执行结果: 
LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- --------------------------- 
Whalen                               10 Administration 
Hartstein                            20 Marketing 
Fay                                  20 Marketing 
Mavris                               40 Human Resources 
Baer                                 70 Public Relations 
Higgins                             110 Accounting 
Gietz                               110 Accounting 
                                        NOC 
                                        Manufacturing 
                                        Government Sales 
                                        IT Support 

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- --------------------------- 
                                        Benefits 
                                        Shareholder Services 
                                        Retail Sales 
                                        Control And Credit 
                                        Recruiting 
                                        Operations 
                                        Treasury 
                                        Payroll 
                                        Corporate Tax 
                                        Construction 
                                        Contracting 


以上语句可以写成: 
SELECT e.last_name, e.department_id, d.department_name 
FROM employees e, departments d 
WHERE d.department_id = e.department_id (+); 

也可以写成:(执行结果一样) 
SQL> SELECT e.last_name, e.department_id, d.department_name 
  2  FROM employees e, departments d 
  3  where e.department_id(+)=d.department_id; 




全外连接 

该查询取回 EMPLOYEES 表中所有的行，即使在 DEPARTMENTS 表中没有相匹配的行。它也取回 DEPARTMENTS 表中所有的行，即使 EMPLOYEES 表中没有相匹配的行。 

SQL> SELECT e.last_name, e.department_id, d.department_name 
  2  from employees e 
  3  full OUTER JOIN departments d 
  4  on (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 
UNION 
SELECT e.last_name, e.department_id, d.department_name 
FROM employees e, departments d 
WHERE e.department_id = d.department_id (+); 

执行结果: 
LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- ------------------------- 
Urman                               100 Finance 
Sciarra                             100 Finance 
Chen                                100 Finance 
Faviet                              100 Finance 
Greenberg                           100 Finance 
Gietz                               110 Accounting 
Higgins                             110 Accounting 
Grant 
                                        NOC 
                                        Manufacturing 
                                        Government Sales 

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- ------------------------- 
                                        IT Support 
                                        Benefits 
                                        Shareholder Services 
                                        Retail Sales 
                                        Control And Credit 
                                        Recruiting 
                                        Operations 
                                        Treasury 
                                        Payroll 
                                        Corporate Tax 
                                        Construction 

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME 
------------------------- ------------- ------------------------- 
                                        Contracting 
                                        IT Helpdesk