Oracle Combined Query

Combined Query, as its name implies, combines two or more tables to perform complex queries. The combined query is a bit similar to the high mathematics: Set. It is further divided into: full set, subset, union, and intersection. Preview the tables to be used: Table _ 1: [SQL] SQL> SELECT * FROM EMPCHK; invalid EMP_ID EMP_NAME parameter EMP_ZIP parameter EMP_CITY EMP_PHONE certificate --------- certificate ------- certificate ----------- ---------- 100000 zou qi lei ban tian bulong street 518000 yang mei village shen zhen 123456789 3500 CAI FANG JIE BAN TIAN BULONG STR EET 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 100004 li wei qing shan gang du hua yuan 430060 xiao qu wu han 197 *** 436 table _ 2: [SQL] SQL> SELECT * FROM EMPCHKTMP; EMP_ID EMP_NAME EMP_ST_ADDR EMP_ZIP EMP_ADDR EMP_CITY EMP_PHONE EMP_S ALRAY --------- begin certificate ------- begin certificate ----------- ---------- 100001 cai fang jie ban tian bulong street 518000 yang mei village shen zhen 18218429577 100003 wu jing xin hu bei dier shifan xueyuan 430060 ming zhu yuan shen zhen 187 * *** 235 ding ling han kou tai bei lu 100005 tian men dun nearby wu han 430060 *** 137 2 000 [SQL] www.2cto.com UNION ALL: as the name suggests, it is the complete set. For example, [SQL] SQL> SELECT EMP_ADDR FROM EMPCHK UNION ALL SELECT EMP_ST_ADDR FROM EMPCHKTMP ORDER BY EMP_ADDR; EMP_ADDR required ban tian bulong street han kou tai bei lu hu bei dier shifan xueyuan ming zhu yuan www.2cto.com xiao qu yang mei village 8 rows selected can be seen in the display mode EMP_ADDR is used as the column item, this is why order by EMP_ADDR is not EMP_ST_ADDR, and the returned results include EMP_A. All table items of DDR and EMP_ST_ADDR. [SQL] UNION: As the name implies, UNION is the UNION set, and the partition does not contain duplicate table items. For example, [SQL] SQL> SELECT EMP_ST_ADDR FROM EMPCHK UNION SELECT EMP_ADDR FROM EMPCHKTMP ORDER BY EMP_ST_ADDR; www.2cto.com baiyunban tian bulong street fan yu session hu bei dier shifan xueyuan ming zhu yuan qing shan gang du hua yuan tian men dun nearby yang mei village 7 rows selected EMP_ST_ADDR Is a table item, which is why order by EMP_ST_ADDR. That is, when displayed, the query field after the first SELECT is always used as the list item. We can see that only one yang mei village is returned for the UNION combination query. That is, the option that does not duplicate two tables is used, and duplicate table items in the same table are ignored. [SQL] INTERSECT: As the name implies, it is an intersection, that is, two or more tables with the same content. Example: [SQL] SQL> SELECT region FROM EMPCHK INTERSECT SELECT EMP_ST_ADDR FROM EMPCHKTMP; EMP_ST_ADDR limit BAN TIAN BULONG STREET HU BEI DIER SHIFAN XUEYUAN www.2cto.com MINUS, that is, the first table has a table item while the second table does not. for example: [SQL] SQL> SELECT EMP_ST_ADDR FROM EMPCHK MINUS SELECT EMP_ST_ADDR FROM EMPCHKTMP; EMP_ST_ADDR -------------------------- FAN YU SESSION QING SHAN GANG DU YUAN The field [SQL] FAN YU SESSION QING SHAN GANG DU HUA YUAN is not found in the second table EMPCHKTMP.
Conclusion: union all takes the complete set, UNION takes the UNION set (excluding repeated items), INTERSECT takes the intersection, and MINUS takes the subset .. Each query field, that is, the fields following the SELECT statement, must be of the same type and the number must be equal .. add the third query table: EMPCHKSUB, preview: [SQL] SQL> SELECT * FROM EMPCHKSUB; EMP_ID EMP_NAME EMP_PHONE EMP_SALRAY --------- --------------- ----------- ---------- 100001 cai fang jie 18218429577 100002 lu jing huan 1397165903 100003 wu jing xin 187 *** 235 li wei 100004 *** 197 combination query is acceptable combine two or more tables, for example, the following query is performed based on the name field: [SQL] SQL> SELECT EMP_NAME FROM EMPCHK 2 UNION ALL SELECT EMP_NAME FROM EMPCHKTMP 3 UNION ALL SELECT EMP_NAME FROM EMPCHKSUB 4 ORDER BY EMP_NAME; EMP_NAME -------------------- cai fang jie ding ling li wei lu jing huan wu jing xin zou qi lei 12 rows selected
All name fields in the three tables are returned. For example, if you use MINUS for combined queries, the TAB_1-TAB_2-TAB_3 set is returned. [SQL]
SQL> SELECT EMP_NAME FROM EMPCHK 2 MINUS SELECT EMP_NAME FROM EMPCHKTMP 3 MINUS SELECT EMP_NAME FROM EMPCHKSUB 4 ORDER BY EMP_NAME; EMP_NAME -------------------- zou qi lei can see that the name field zou qi lei only exists in EMPCHK and does not exist in EMPCHKTMP and EMPCHKSUB tables.
Author: Free_Program_1314