Oracle外连接(left/right/full outer join)语法详解

原创 2017年01月03日 22:56:25

相比常用的精确查询(内连接,inner join),外连接相比不好理解。但在实际工作中,用的还是很多的,深刻理解外连接成为必须。

看到一篇帖子,清晰易懂,转发一下。

原贴链接:http://blog.chinaunix.net/uid-21187846-id-3288525.html

内容:

--------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle  外连接

(1)左外连接 (左边的表不加限制)
       (2)右外连接(右边的表不加限制)
       (3)全外连接(左右两表都不加限制)

 

     外连接(Outer Join)

outer join则会返回每个满足第一个(顶端)输入与第二个(底端)输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种: 左外连接,右外连接,全外连接。 对应SQL:LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成:LEFT/RIGHT/FULL JOIN。

 

在左外连接和右外连接时都会以一张表为基表,该表的内容会全部显示,然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。

 

对于外连接, 也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项:
       1.(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。
       2. 当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件,则必须在所有条件中都包含(+)操作符
       3.(+)操作符只适用于列,而不能用在表达式上。
       4.(+)操作符不能与or和in操作符一起使用。
       5.(+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。

 


在做实验之前,我们先将dave表和bl里加一些不同的数据。 以方便测试。

SQL> select * from bl;

        ID NAME

---------- ----------

         1 dave

         2 bl

         3 big bird

         4 exc

         9 怀宁

SQL> select * from dave;

        ID NAME

---------- ----------

         8 安庆

         1 dave

         2 bl

         1 bl

         2 dave

         3 dba

         4 sf-express

         5 dmm

2.1 左外连接(Left outer join/ left join)

     left join是以左表的记录为基础的,示例中Dave可以看成左表,BL可以看成右表,它的结果集是Dave表中的数据,在加上Dave表和BL表匹配的数据。换句话说,左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.

 

示例:

SQL> select * from dave a left join bl b on a.id = b.id;

 

       ID NAME               ID NAME

--------- ---------- ---------- ----------

        1 bl                  1 dave

        1 dave                1 dave

        2 dave                2 bl

        2 bl                  2 bl

        3 dba                 3 big bird

        4 sf-express          4 exc

        5 dmm                             -- 此处B表为null,因为没有匹配到

        8 安庆                             -- 此处B表为null,因为没有匹配到

SQL> select * from dave a left outer join bl b on a.id = b.id;

 

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         1 bl                  1 dave

         1 dave                1 dave

         2 dave                2 bl

         2 bl                  2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

         5 dmm

         8 安庆

 

用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。所以加号写在右表,左表就是全部显示,故是左连接。

 

SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意: 用(+) 就要用关键字where

 

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         1 bl                  1 dave

         1 dave                1 dave

         2 dave                2 bl

         2 bl                  2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

         5 dmm

         8 安庆

    

2.2 右外连接(right outer join/ right join)

和left join的结果刚好相反,是以右表(BL)为基础的, 显示BL表的所以记录,在加上Dave和BL 匹配的结果。 Dave表不足的地方用NULL填充.

 

示例:

SQL> select * from dave a right join bl b on a.id = b.id;

 

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

                               9 怀宁    --此处左表不足用Null 填充

已选择7行。

SQL> select * from dave a right outer join bl b on a.id = b.id;

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

                               9 怀宁  --此处左表不足用Null 填充

已选择7行。

 

 

用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。所以加号写在左表,右表就是全部显示,故是右连接。

 

SQL> Select * from dave a,bl b where a.id(+)=b.id;

 

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

                               9 怀宁

 

2.3 全外连接(full outer join/ full join)

     左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充。 全外连接不支持(+)这种写法。

 

示例:

 

SQL> select * from dave a full join bl b on a.id = b.id;

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         8 安庆

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

         5 dmm

                               9 怀宁

 

已选择9行。

 

SQL> select * from dave a full outer join bl b on a.id = b.id;

        ID NAME               ID NAME

---------- ---------- ---------- ----------

         8 安庆

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

         5 dmm     


Oracle Database SQL Language Reference中关于outer join的描述:

-----------------------------------------------------------------------------------------------------------

Outer Joins
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for
which no rows from the other satisfy the join condition.
■ To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or
apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database
returns null for any select list expressions containing columns of B.
■ To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or
apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns
null for any select list expressions containing columns of A.
■ To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use
the FULL [OUTER] JOIN syntax in the FROM clause.
You cannot compare a column with a subquery in the WHERE clause of any outer join, regardless which form you specify.
You can use outer joins to fill gaps in sparse data. Such a join is called a partitioned
outer join and is formed using the query_partition_clause of the join_clause syntax. Sparse data is data that does not have rows for all possible values of a dimension such as time or department. For example, tables of sales data typically do not have rows for products that had no sales on a given date. Filling data gaps is useful in situations where data sparsity complicates analytic computation or where some data might be missed if the sparse data is queried directly.

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
■ You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
■ The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
■ If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
■ The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
■ You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
-- The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;
However, the following self join is valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id
ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;
■ The (+) operator can be applied only to a column, not to an arbitrary expression.
However, an arbitrary expression can contain one or more columns marked with the (+) operator.
■ A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
■ A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns
the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply
the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT on page 19-4 for the syntax for an outer join.


版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

oracle full join on的用法

create or replace view view_jyxkz_tj as --yxq为有效期,ZXRQ为注销时间,FZRQ为发证日期 select t.dqxx_id,decode(t.yx...

图解SQL的inner join、left join、right join、full outer join、union、union all的区别

对于SQL的Join,在学习起来可能是比较乱的。我们知道,SQL的Join语法有很多inner的,有outer的,有left的,有时候,对于Select出来的结果集是什么样子有点不是很清楚。Codin...

hive 各种 join (left outer join、join、full outer join)

一、概念 1、左连接 left outer join 以左边表为准,逐条去右边表找相同字段,如果有多条会依次列出 2、连接join 找出左右相同同的记录 二、实验 1、准备数据 create ex...

SQL中的left outer join,inner join,right outer join用法详解

SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 连接类型                                     ...

★★★ oracle外连接,Oracle中Left Outer Join和外关联(+)的区别

oracle外连接,Left Outer Join和外关联(+)的区别
  • jackpk
  • jackpk
  • 2010年10月27日 14:22
  • 8700

cross apply 和 outer apply

使用APPLY运算符可以实现查询操作的外部表表达式返回的每个调用表值函数。表值函数作为右输入,外部表表达式作为左输入。 通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输...

彻底搞懂Oracle的左外连接和右外连接(以数据说话)

Employees表:Department表:select e.last_name,e.department_id,d.department_name from employees e left ou...
  • liuxiIT
  • liuxiIT
  • 2010年08月05日 18:00
  • 25885

oracle 外连接的一个实例

有四个表:员工分配表:per_all_assignments_f员工职务表:per_jobs员工职务多语言表:per_jobs_tl员工职位表:hr_locations_all_tl  要求:查出所有...

Oracle 表连接方式详解(外链接、内连接、自连接)

Oracle 表之间的连接分为三种: 1. 内连接(自然连接) 2. 外连接 (1)左外连接 (左边的表不加限制)        (2)右外连接(右边的表不加限制)         (3)全外连...

Oracle外连接中对非连接条件使用(+)需要注意的地方

1.先来说下Oracle外连接语句中对非链接条件使用(+)的作用问题 之前问过朋友,当时大脑处于短路状态,居然没想明白作用是啥。先看例子如下: select * from dept,emp whe...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle外连接(left/right/full outer join)语法详解
举报原因:
原因补充:

(最多只允许输入30个字)