Oracle External commands

Oracle External combined command oracle External combined command: To understand (+), the most important thing is that (+) is always placed on the side where information is scarce, and records on this side are displayed as null values. Example: preview the two tables that we will combine: EMPLOYEE_CHECK and EMPLOYE_CHECK_TMP. @ _ 1 Table EMPLOYEE_CHECK: www.2cto.com [SQL] SQL> SELECT * FROM EMPLOYEE_CHECK; EMP_ID EMP_NAME unzip EMP_ZIP EMP_ADDR EMP_CITY EMP_PHONE EMP_SALRAY --------- zookeeper ------- zookeeper ----------- ---------- 100000 zou qi lei ban tian bulong street 518000 yang mei village shen zhen 123456789 3500 100001 CAI FANG JIE T BAN Ian bulong street 518000 yang mei village shen zhen 18218429577 100002 lu jing huan fan yu session 430060 yang mei village guang zhou 1397165903 100003 wu jing xin hu bei dier shifan xueyuan 430060 ming zhu yuan wu han 187 * *** 235 www.2cto.com 100004 li wei qing shan gang du hua yuan 430060 xiao qu wu han 197 *** 436 @ _ 2 Table EMPLOYEE_CHECK_TMP: [SQL] SQL> SELECT * FROM EMPLOYEE_CHECK_TMP; EMP_ID EMP_NA ME export EMP_ZIP export EMP_CITY EMP_PHONE EMP_SALRAY --------- hour ------- hour ---------------- 100001 cai fang jie ban tian bulong street 518000 yang mei village shen zhen 18218429577 wu jing xin hu bei dier shifan limit 100003 ming zhu yuan shen zhen 187 *** 235 DING LING HAN KOU T Ai bei lu 430060 tian men dun nearby wu han 137 *** 4256 now combines the two tables and queries with '+: [SQL] SQL> SELECT <span style = "color: # ff6666;"> P. EMP_NAME, P. EMP_ADDR, P. EMP_CITY, P. EMP_PHONE </span>, <span style = "color: # 3366ff;"> T. EMP_ZIP </span> FROM EMPLOYEE_CHECK P, EMPLOYEE_CHECK_TMP t where <span style = "color: # ff6600;"> P. EMP_ID </span> <span style = "color: # 3366ff;"> </span> = <span style = "color: # 3366ff;"> T. EMP _ ID (+) </span>; export EMP_NAME EMP_ADDR EMP_CITY EMP_PHONE EMP_ZIP {-------------------- ----------- zou qi lei yang mei village shen zhen 123456789 -- row_1 cai fang jie yang mei village shen zhen 18218429577 lu jing huan yang mei village guang zu 1397165903 -- row_3 wu jing xin ming zhu yuan wu han 187 *** 235 LI WEI XIAO QU WU H AN 197*436-row_5 now observes two tables combined with queries. The primary table (P) is marked in red and the query field is [SQL] <span style = "color: rgb (1, 255,102,102); "> P. EMP_NAME, P. EMP_ADDR, P. EMP_CITY, P. EMP_PHONE </span>: the temporary table marked in blue (T). The query field is T. EMP_ZIP www.2cto.com when '+' (combined with the flag) is attached to the T table, besides returning where p that meets the query condition. EMP_ID = T. in addition to EMP_ID information, the query fields corresponding to P are also returned: row_1, row_3, and row_5. In reverse query, combine the '+' flag with the primary table P: [SQL] <span style = "font-family: verdana, arial, helvetica;"> SQL> SELECT P. EMP_NAME, P. EMP_ADDR, P. EMP_CITY, P. EMP_PHONE, T. EMP_ZIP FROM EMPLOYEE_CHECK P, EMPLOYEE_CHECK_TMP t where p. EMP_ID (+) = T. EMP_ID; EMP_NAME EMP_ADDR EMP_CITY EMP_PHONE EMP_ZIP ---------------------- ------------------ ----------------------- ------- cai fang jie yang mei village shen zhen 18218429 577 518000 wu jing xin ming zhu yuan wu han 187 *** 235 430060 430060 </span> -- besides the query information that meets the conditions, other fields in table T are also returned. Of course, because only EMP_ZIP fields are queried in table T, all other fields are NULL. A more vivid query structure is shown in the following figure: from t right outer join p on t. EMP_ID = P. EMP_ID; --- @ _ 1 [SQL] <span style = "font-family: verdana, arial, helvetica;"> SQL> SELECT P. EMP_NAME, P. EMP_ADDR, P. EMP_CITY, P. EMP_PHONE, T. EMP_ZIP FROM EMPLOYEE_CHECK p right outer join EMPLOYEE_CHECK_TMP t on p. EMP_ID = T. EMP_ID; www.2cto.com EMP_NAME EMP_ADDR EMP_CITY EMP_PHONE EMP_ZIP ----------------------------------------------------------- ------------ ------- Cai fang jie yang mei village shen zhen 18218429577 518000 wu jing xin ming zhu yuan wu han 187 *** 235 430060 430060 </span> when right outer join is used, the table on the right is the T table. In addition to meeting the query conditions, all other fields in the T table are returned. A more vivid query structure is shown in the following figure: from t left outer join p on t. EMP_ID = P. EMP_ID; --- @ _ 2 [SQL] <span style = "font-family: verdana, arial, helvetica;"> SQL> SELECT P. EMP_NAME, P. EMP_ADDR, P. EMP_CITY, P. EMP_PHONE, T. EMP_ZIP FROM EMPLOYEE_CHECK p left outer join EMPLOYEE_CHECK_TMP t on p. EMP_ID = T. EMP_ID; www.2cto.com EMP_NAME EMP_ADDR EMP_CITY EMP_PHONE EMP_ZIP ------------------------------------------------------------ ----------- ------- Zou qi lei yang mei village shen zhen 123456789 cai fang jie yang mei village shen zhen 18218429577 518000 lu jing huan yang mei village guang zhou 1397165903 wu jing xin ming zhu yuan wu han 187 * * ** 235 430060 li wei xiao qu wu han 197 *** 436 </span> but when left outer join is used, the table on the left is the P table. All fields in the P table are returned in addition to meeting the query conditions. Of course, there is also from all outer join. Author Free_Program_1314