oracle left outer join and right outer join, full outer joins

2011-08-01  来源:本站原创  分类:Database  人气:136 

For the outer join, Oracle can use  "(+)"  To represent, 9i can be used  LEFT/RIGHT/FULL OUTER JOIN,  The following examples will be introduced one by one with  .

  1. LEFT OUTER JOIN:  Left outside the association  

  SELECT e.last_name, e.department_id, d.department_name

  FROM employees e

  LEFT OUTER JOIN departments d

  ON (e.department_id = d.department_id);

    Equivalent to  

  SELECT e.last_name, e.department_id, d.department_name

  FROM employees e, departments d

  WHERE e.department_id=d.department_id(+);

    Results  :  All records of staff and counterparts, including the department number does not correspond to  department_id  Employee records  .

  2. RIGHT OUTER JOIN:  Right outside the association  

  SELECT e.last_name, e.department_id, d.department_name

  FROM employees e

  RIGHT OUTER JOIN departments d

  ON (e.department_id = d.department_id);

    Equivalent to  

  SELECT e.last_name, e.department_id, d.department_name

  FROM employees e, departments d

  WHERE e.department_id(+)=d.department_id;

    Results  :  All records of staff and counterparts, including any employee of the department record  .

  3. FULL OUTER JOIN:  Associated with all external  

  SELECT e.last_name, e.department_id, d.department_name

  FROM employees e

  FULL OUTER JOIN departments d

  ON (e.department_id = d.department_id);

    Results  :  All records of staff and counterparts, including the department number does not correspond to  department_id  Employee records and no record of any employee of the department  .

    Outer join  :

    In addition to showing the same connection conditions match the data, but also can display a table in a join condition can not match the record of the same  !

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

  1)   Left condition  (+) =   The right conditions  

   Conditions left the table where the join condition must be strictly equal to the match, while the right conditions, where the table in addition to matching the same connection conditions  ,  Can also display data can not match the join condition  !

    Also known as a right outer join  .

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

    Can be replaced with the following statements  :

  SELECT...FROM   Table 1 RIGHT OUTER JOIN table  2 ON   Condition  ;

  2)   Left = right conditions, the conditions  (+)

    The right conditions, where the table must be strictly connected to the same conditions of the match, and left the table in addition to matching conditions where the same connection conditions  ,  Can also display data can not match the join condition  !

    Also known as a left outer join  .

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

    Can be replaced with the following statements  :

  SELECT...FROM   Table 1 LEFT OUTER JOIN table  2 ON   Condition  ;

  REM   In addition to the same connection, the display information without employee's department  .

  SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

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

  REM   In addition to the same connection, the display no department employee information  .

  SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);

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

  Oracle   I suggest you use the statement to use in the From  Outer Join  Syntax instead of  Oracle  The Join operator  (+).  And  (+)  Is limited by the following rules, but  Outer Join  Syntax is not subject to the  --

  1)  You can not use the blocks in the query  (+)   When it contains both the join  from  Statement  

  2)(+)  Only in the where statement  ,  And only the corresponding row of a table or view field  

  3)  If A and  B  Do when there are multiple conditions connected, then  (+)  Must complete all of the matching conditions  ,

    If not, oracle will not warn you  -  Just the natural result of different  

  4)  Can not make a sheet in the other tables in the query query  (+)  Join operations  --

  5)  Not use  (+)  External link to its own course  Self Join  Is the  

  6)  Contain (+)  Where's attention after the  

  OR  Unavailable  

  IN  Unavailable  

    Sub-query is not available  

    The following give some examples  :

  SQL code

  SQL> desc part

  Name                                      Null?    Type

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

  PART_ID                                   NOT NULL VARCHAR2(4)

  SUPPLIER_ID                                        VARCHAR2(4)

  SQL> select * from part;

  PART SUPP

  ---- ----

  P1   S1

  P3

  P4
相关文章
  • sql oracle performance optimization of the two table join table association 2010-03-11

    sql oracle performance optimization of the two table join table association

  • [Sql tuning of the implementation plan] merge semi join and merge anti join 2010-10-27

    [Sql tuning of the implementation plan] merge semi join and merge anti join Semi Join (also called half-connections) or more in the sub-query in the use of such exists, for the outer row set, look for the internal (ie, sub-query) row set, match back

  • multi-table join sql query inner join, left join, right join, full join, cross join study 2010-04-02

    Easy to understand: inner join, full outer join, left join, right jion Interconnect inner join to meet a combination of the two tables full outer-wide with the same two tables together, A table has, B does not have the data table (shown as null), tab

  • Bank of China transferred to the Oracle database and the use of summary Join 2011-08-25

    Bank of China transferred to the Oracle database and Join Our main usage is to introduce this content, we know that in the Bank of China transferred to Oracle, you can use decode function to achieve, we assume that students have the following table A

  • Oracle关于left join on-and 及 left join on...whe... 2013-05-30

    关于left join...on...and...以及left join...on...where的区别,网上很多的说法是对于left join...on...and...,and所限定的条件只对关联字段起作用,比如select a.* from tmp_table_a a left join tmp_table_b b on a.col1=b.col1 and b.col2=xx,很多人认为条件b.col2=xx是不起作用的. 对于这种说法,我个人是不认同的,至少来讲,这是一种不负责任的说法.

  • Oracle nested tables and arrays use (eliminating join queries) 2010-07-27

    The use of Oracle nested table 1. Create object type create or replace type scott.depscore_type as OBJECT ( depid number (4), score number ); 2. Create a table type create or replace type scott.depscore_tab_type as table of scott.depscore_type; 3. To

  • Sql join queries Daquan, left join, right join, full join 2011-07-18

    inner join, full outer join, left join, right jion Internal connection table inner join to meet a combination of two full outer two tables full with the same combination together, A table has, B does not have the data table (shown as null), Table B h

  • left join on and left join where the difference between 2010-12-07

    By connecting two or more database tables to return the records, will generate a middle of the temporary table, then this temporary table to return to the user. When using the left jion in, on, and where the difference between the conditions are as f

  • oracle outer join query to connect all connections within the connection 2010-03-12

    oracle of the connection can be divided into, in connection (inner join), outer join (outer join), fully connected (full join), not only Oracle, the database also have many other these three kinds of ways to connect query In connection inner join, th

  • Oracle outer join on a number of issues 2010-03-24

    In connection (inner join), fully connected (full join), the left link (left join), the right connection (right join) Oracle to connect the left and right connections PL-SQL, the left and right connections to connect to the following method to achiev

  • study notes oracle outer join queries Xiangjie _ 2010-05-14

    Stresses outside the connection before examples of internal connections, which is generally equal to connection. select * from a, b where a.id = b.id; For external connections, oracle can be expressed using "(+)", 9i can use the left / right / f

  • Oracle outer join and the "+" sign usage 2010-09-27

    For external connectivity, Oracle can use "(+)" to represent, 9i can use the LEFT / RIGHT / FULL OUTER JOIN, the following will be introduced with examples of 11. 1. LEFT OUTER JOIN: left lateral association SELECT e.last_name, e.department_id,

  • Oracle internal connections. External connections. The right outer join. Full outer join a small sum 2010-09-27

    Database Version: Oracle 9i Table TESTA, TESTB, TESTC, each A, B two TestA AB 001 10A 002 20A TESTB AB 001 10B 003 20B TESTC AB 001 10C 004 40C Connection of two kinds: in connection with the outside connections. A. In connection The connection, that

  • Oracle table connection (inner / outer join / self connection) 2010-10-30

    Oracle table connection (inner / outer join / self connection) Detailed Oracle table connection (inner / outer join / self connection) Detailed The connection between the Oracle table is divided into three types: 1. The connection (natural join) 2. O

  • oracle sql inner join outer join query to connect the whole connection 2010-08-23

    the oracle connection can be divided into, in connection (inner join), outer join (outer join), fully connected (full join), not only Oracle, the database also have many other of these three ways to search for connections Connections within the inner

  • Detailed information about Oracle outer join 2011-07-06

    Outer join. Outer joins can be left outer join, right outer join or full outer join. Specified in the FROM clause outer join can be conducted by the following groups of keywords in a group designated: LEFT JOIN or LEFT OUTER JOIN. Left outer join res

  • Oracle table connection (inner / outer join / self-connection) Detailed. 2011-08-02

    The connection between the Oracle table is divided into three types: 1 in connection (natural connection) 2 outer joins (1) left outer join (on the left of the table without restriction) (2) right outer join (the right side of the table without restr

  • Use of Partitioned Outer Join to achieve thickening Report 2010-09-06

    Use of Partitioned Outer Join to achieve thickening Report Analysis: Ding (dingjun123) Background: In the database table, the stored data is often sparse data (sparse data), rather than the dense data (dense data). First to look at what is sparse dat

  • Using Partitioned Outer Join to achieve thickening report 2010-09-06

    Using Partitioned Outer Join to achieve thickening report Author: Ding (dingjun123) Background: In the database table, the stored data is often sparse data (sparse data), rather than the dense data (dense data). Let's look at what is sparse data, suc

  • SQL connection (inner, left join, right outer joins, self-connection) Detailed 2011-03-17

    create table department( id number primary key, name varchar2(30)); create table employee( id number primary key, name varchar2(30) not null, salary number(6,2), title varchar2(30), manager number, deptid number); insert into department values(1,' Hu