`
jessen163
  • 浏览: 464741 次
  • 性别: Icon_minigender_1
  • 来自: 潘多拉
社区版块
存档分类
最新评论

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
分享到:
评论

相关推荐

    oracle表连接学习总结(火)

    oracle表连接学习总结(火) oracle表连接学习总结(火) oracle表连接学习总结(火)

    ORACLE表连接方式

    本文将对Oracle中的几种主要表连接方式进行详细介绍:内连接(Inner Join)、自然连接(Natural Join)、左外连接(Left Outer Join)、右外连接(Right Outer Join)、笛卡尔连接(Cartesian Join)、索引连接...

    oracle多表连接技巧

    ### Oracle多表连接技巧详解 #### 一、引言 在Oracle数据库中,多表连接是一种非常实用且常用的技术,它允许用户从多个表中获取数据,并基于特定条件进行匹配和整合。本文将详细介绍Oracle中多表连接的各种类型及其...

    Oracle数据库3种主要表连接方式对比

    ### Oracle数据库三种主要表连接方式对比 在Oracle数据库中,为了获取来自多个表的数据,我们需要使用不同的连接方式来实现这一目的。本文将详细介绍三种主要的表连接方式:嵌套循环连接(Nested Loop Join,简称NL...

    Oracle中表的连接及其调整.

    总结来说,理解 Oracle 中的表连接方法和它们的适用场景是提高 SQL 查询性能的关键。通过合理选择连接方式,配合索引策略,我们可以显著减少查询时间,提升数据库应用的响应速度。在实际工作中,应结合具体业务需求...

    Oracle SQL连接查询总结.docx

    ### Oracle SQL连接查询总结 #### 一、概述 在Oracle SQL中,连接查询是一种非常重要的技术,用于结合两个或多个表中的数据。连接查询能够帮助我们处理复杂的数据关系,提取出满足特定条件的信息。根据连接的方式...

    oracle数据库表左连接右连接全连接的认识

    Oracle 数据库表连接(Join)知识点总结 Oracle 数据库表连接(Join)是指从两个或多个表中检索数据,并且将它们组合成一个结果集的操作。连接操作可以大大提高数据库查询的效率,特别是在需要访问多个表中的字段...

    Postgresql连接oracle驱动并实现读写

    ### Postgresql连接Oracle数据库并通过oracle_fdw驱动实现读写操作 #### 一、概述 本文档将详细介绍如何在CentOS 6.2环境下通过PostgreSQL 9.5.6使用`oracle_fdw`驱动程序来连接Oracle 11.2客户端,并实现数据的...

    Oracle连接字符串

    Oracle 连接字符串是指连接 Oracle 数据库的字符串书写方法,本文总结了多种连接 Oracle 数据库的字符串书写方法。 一、标准连接字符串 标准连接字符串是最基本的连接字符串格式,用于连接 Oracle 数据库。例如,...

    oracle修改最大连接数

    ### Oracle 修改最大连接数 在Oracle数据库环境中,调整最大连接数是一项重要的系统管理任务,它直接影响到数据库能够同时处理的会话数量。本篇将详细探讨如何通过Oracle参数来修改最大连接数,包括理解相关参数的...

    linux远程连接oracle数据库

    ### Linux远程连接Oracle数据库知识点详解 #### 一、测试项目 - **通过Java程序远程连接Oracle 10g数据库** - Java作为一种广泛使用的编程语言,可以用来开发各种应用程序,包括与数据库交互的应用。要实现Java...

    oracle 数据库,在C++中用连接池实现高速连接与访问.rar

    四、在C++中实现Oracle连接池 1. 初始化:首先,需要初始化环境和会话,设置必要的参数,如数据库服务器地址、端口、用户名、密码等。 2. 创建连接池:通过oci池化函数ociPoolCreate创建连接池,指定最小、最大连接...

    Oracle左连接,右连接.doc

    ### Oracle左连接与右连接详解 #### 一、概述 在数据库操作中,表与表之间的连接是非常重要的操作之一。连接操作可以将多个表的数据整合在一起,形成更完整的信息视图。根据不同的需求,Oracle提供了多种连接方式...

    JAVA 使用数据库连接池连接Oracle数据库全代码

    ### JAVA 使用数据库连接池连接Oracle数据库全代码解析 #### 一、概述 本文将详细介绍如何在Java项目中使用Apache DBCP(Database Connection Pool)来连接Oracle数据库,并提供完整的示例代码。通过这种方式,我们...

    Oracle中表的四种连接方式讲解

    总结来说,Oracle中的表连接提供了丰富的数据组合方式,使我们能够灵活地从多个表中提取所需信息。正确理解和运用这些连接类型是数据库查询和数据分析的基础。在实际应用中,应根据具体需求选择合适的连接类型,以...

    oracle连接数问题

    通过细致地调整Oracle数据库的`pga_aggregate_target`、`processes`、`sessions`参数,以及优化`LISTENER.ORA`文件和操作系统配置,可以有效解决Oracle连接数问题,提升数据库的并发处理能力。这些操作需要根据具体...

    更改Oracle数据库的用户连接数

    ### 更改Oracle数据库的用户连接数 在Oracle数据库管理中,有时我们可能会遇到因为数据库连接过多而导致的新连接请求被拒绝的问题。这种情况不仅会影响用户的正常使用,还可能导致系统性能下降甚至数据库重启。因此...

    oracle学习心得总结

    ### Oracle学习心得总结 在IT领域中,Oracle数据库因其强大的功能和广泛的适用性而备受青睐。对于初学者而言,掌握Oracle的基本操作是非常重要的一步。本文将根据提供的文档内容,总结和扩展Oracle数据库的基础知识...

Global site tag (gtag.js) - Google Analytics