Oracle left join, right join and full join

An Introduction to left join, right join and full join in oracle is from oracle ocp9i document: outer join syntax: 1) you use an outer join to also see rows that do not meet the join condition 2) the outer join operator is the plus sign (+) outer join restrictions: 1) the outer join operator can appear on only one side of the expression: the side that has information missing. it returns those rows from one table that have no direct mat Ch in the other table. www.2cto.com 2) a condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator. configure the experiment environment: [SQL] hr @ ORCL> drop table a; hr @ ORCL> drop table B; hr @ ORCL> create table a (id number, name varchar2 (10); hr @ ORCL> create table B (id number, name varchar2 (10); hr @ ORCL> insert into a values (1, 'A'); hr @ ORCL> insert into a values (2, 'B'); h R @ ORCL> insert into a values (3, 'C'); hr @ ORCL> insert into B values (1, 'A '); hr @ ORCL> insert into B values (2, 'B'); hr @ ORCL> insert into B values (4, 'D '); hr @ ORCL> select * from a; id name ---------- 1 a 2 B 3 c hr @ ORCL> select * from B; id name ---------- 1 a 2 B 4 d hr @ ORCL> commit; -- the result of all external connections is: 1) select all results that meet the conditions. 2) Take the left table as the standard, link the results of the Left table that do not meet the conditions to the left. 3) Take the right table as the standard. link the results of the right table that do not meet the conditions to the right. 4) combine all the above results with www.2cto. Com [SQL] hr @ ORCL> select. id, B. id from a full join B on. id = B. id; ID ---------- 1 1 2 2 3 4 -- left out join and oracle plus signs are the same in the right result. Similarly, right out join is the same as the plus sign on the left. (Comparison between sql99 syntax and oracle private syntax) [SQL] hr @ ORCL> select. id, B. id from a, B where. id = B. id (+); ID ---------- 1 1 2 2 3 hr @ ORCL> select. id, B. id from a left outer join B on. id = B. id; ID ---------- 1 1 2 2 3 -- the result is similar to from a left join B on. col = B. col and. coln = ..... If a single column is selected, the base table (who has the plus sign on and who is from the table, and the base table without the plus sign) is rewritten with decode and +, and cannot be rewritten in general, if it is not a base table, you can simply use + rewrite. For example, [SQL] SQL> SELECT. ID, B. ID 2 FROM a full join B 3 ON. ID = B. id and. NAME = 'a'; ID ---------- 1 1 2 3 1 4 2 rewrite: SQL> SELECT. ID, B. ID 2 FROM a, B WHERE. id = decode (. NAME, 'A', B. ID (+) 3 union all www.2cto.com 4 SELECT. ID, B. ID 5 FROM a, B WHERE. ID (+) = B. id and. NAME (+) = 'A' AND. id is null; ID ---------- 1 1 2 3 1 1 2 4 use union to implement the full join result in the above example. The table relationship needs to be considered. 1) if the plus sign is left for two tables, the plus sign is right (equivalent to the right join of sql99), and the plus sign is right, and the left prevails (equivalent to the left join of sql99) [SQL] hr @ ORCL> select. id, B. id from a, B where. id = B. id (+) 2 union 3 select. id, B. id from a, B where. id (+) = B. id; ID ---------- 1 1 2 2 3 3 4 2) if there are two tables 1: n, it is incorrect to remove the weight with union. [SQL] hr @ ORCL> insert into a values (1, 'A'); hr @ ORCL> commit; hr @ ORCL> select * from; id name ---------- 1 a 2 B 3 c 1 a hr @ ORCL> select * from B; id name ---------- 1 a 2 B 4 d -- 1: n incorrect SQL statement with UNION> SELECT. ID, B. ID 2 FROM a, B WHERE. ID = B. ID (+) 3 UNION 4 SELECT. ID, B. ID 5 FROM a, B WHERE. ID (+) = B. ID; ID ---------- 1 1 2 2 3 4 -- there are three correct solutions. Note that null must be considered whenever and wherever SQL is used !!! Remember. Method 1: www.2cto.com SQL> SELECT. ID, B. ID 2 FROM a, B WHERE. ID = B. ID (+) 3 union all 4 SELECT. ID, B. ID 5 FROM a, B WHERE. ID (+) = B. id and. id is null; ID ---------- 1 1 1 1 2 2 3 4 Method 2: SQL> SELECT. ID, B. ID 2 FROM a, B WHERE. ID = B. ID (+) AND B. id is null 3 union all 4 SELECT. ID, B. ID 5 FROM a, B WHERE. ID (+) = B. ID; ID ---------- 3 1 1 1 1 2 2 2 4 method 3: SQL> SELECT. ID, B. ID 2 F ROM a full join B 3 ON a. ID = B. ID; ID ---------- 1 1 1 1 2 2 3 4 -- the comma and full join are different. In addition, the full join statement must contain the keyword on. Www.2cto.com [SQL] hr @ ORCL> select p. id, t. id from p, t where p. id = t. id; ID ---------- 1 1 3 3 hr @ ORCL> select p. id, t. id from p full join t on p. id = t. id; ID ---------- 1 1 3 3 3 2 5 all outer connections and union connections can achieve the same results. Let's take a look at their execution plan. [SQL] execution plan for all external connections: hr @ ORCL> select. id, B. id from a full join B on. id = B. id; ID ---------- 1 1 2 2 3 3 4 Execution Plan -------------------------------------------------------- Plan hash value: 2192011130 www.2cto.com networks | Id | Operation | Name | Rows | Bytes | <u> <span style = "color: # ff0000;"> Cost (% CPU </span> </U>) | Time | minute | 0 | select statement | 4 | 104 | 13 (8) | 00:00:01 | 1 | VIEW | 4 | 104 | 13 (8) | 00:00:01 | 2 | UNION-ALL | * 3 | hash join outer | 3 | 312 | 7 (15) | 00:00:01 | 4 | table access full | A | 3 | 195 | 3 (0) | 00:00:01 | 5 | table access full | B | 3 | 117 | 3 (0) | 00:00:01 | * 6 | HASH Join anti | 1 | 26 | 7 (15) | 00:00:01 | 7 | table access full | B | 3 | 39 | 3 (0) | 00:00:01 | 8 | table access full | A | 3 | 39 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("". "ID" = "B ". "ID" (+) 6-access ("". "ID" = "B ". "ID ") Note ------dynamic sampling used for this statement Statistics defaults 0 recursive cballs 0 db block gets 29 consistent gets 0 physical reads 0 redo size 520 bytes sent via SQL * Net to client 385 bytes encoded ed SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed union execution plan: hr @ ORCL> select. Id, B. id from a, B where. id = B. id (+) 2 union www.2cto.com 3 select. id, B. id from a, B where. id (+) = B. id; ID Id ---------- 1 1 2 2 3 3 4 Execution Plan hash value: 891669117 Bytes | id | Operation | Name | Rows | Bytes | <span style = "color: # ff0000; "> Cost (% CPU </span>) | Time | --------------------------------------------------------------------------- | 0 | select statement | 6 | 156 | 15 (60) | 00:00:01 | 1 | sort unique | 6 | 156 | 15 (60) | 00:00:01 | 2 | UNION-ALL | * 3 | hash join outer | 3 | 78 | 7 (15) | 00:00:01 | 4 | table access full | A | 3 | 39 | 3 (0) | 00:00:01 | 5 | table access full | B | 3 | 39 | 3 (0) | 00:00:0 1 | * 6 | hash join outer | 3 | 78 | 7 (15) | 00:00:01 | 7 | table access full | B | 3 | 39 | 3 (0) | 00:00:01 | 8 | table access full | A | 3 | 39 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("". "ID" = "B ". "ID" (+) 6-access ("". "ID" (+) = "B ". "ID ") note ------dynamic sampling used for this statement Statistics www.2cto.com defaults 0 recursive call0 db block gets 28 consistent gets 0 physical reads 0 redo size 520 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client <span style = "color: # ff0000; "> 1 sorts (memory) </Span> 0 sorts (disk) 4 rows processed obviously, the cpu cost of the union connection is much higher than that of the full join connection. In addition, union also implies a sort operation. When the data volume is large, it is estimated that the performance will be affected. In addition, there is no full outer join in the private Syntax of oracle, and full join can only be simulated through the union join. Therefore, we recommend that you use full join instead of union simulation when using outer join. Author: linwaterbin