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.

First, preview the tables to be used:

Table _ 1:

SQL> SELECT * FROM EMPCHK; EMP_ID    EMP_NAME             EMP_ST_ADDR                    EMP_ZIP EMP_ADDR             EMP_CITY             EMP_PHONE   EMP_SALRAY--------- -------------------- ------------------------------ ------- -------------------- -------------------- ----------- ----------100000    ZOU QI LEI           BAN TIAN BULONG STREET         518000  YANG MEI VILLAGE     SHEN ZHEN            123456789         3500100001    CAI FANG JIE         BAN TIAN 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  100004    LI WEI               QING SHAN GANG DU HUA YUAN     430060  XIAO QU              WU HAN               197****436  

Table _ 2:

SQL> SELECT * FROM EMPCHKTMP; EMP_ID    EMP_NAME             EMP_ST_ADDR                    EMP_ZIP EMP_ADDR             EMP_CITY             EMP_PHONE   EMP_SALRAY--------- -------------------- ------------------------------ ------- -------------------- -------------------- ----------- ----------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  100005    DING LING            HAN KOU TAI BEI LU             430060  TIAN MEN DUN NEARBY  WU HAN               137***4256        2000 

Union all: As the name implies, it is the complete set. For example:

SQL> SELECT EMP_ADDR FROM EMPCHK UNION ALL SELECT EMP_ST_ADDR FROM EMPCHKTMP ORDER BY EMP_ADDR ; EMP_ADDR------------------------------BAN TIAN BULONG STREETHAN KOU TAI BEI LUHU BEI DIER SHIFAN XUEYUANMING ZHU YUANXIAO QUYANG MEI VILLAGEYANG MEI VILLAGEYANG MEI VILLAGE 8 rows selected

We can see that EMP_ADDR is used as the column item in the display mode. This is why order by EMP_ADDR is not EMP_ST_ADDR, and the returned results include all table items of EMP_ADDR and EMP_ST_ADDR.

UNION: As the name implies, it is a UNION set, and the partition does not contain duplicate table items. For example:

SQL> SELECT EMP_ST_ADDR FROM EMPCHK UNION SELECT EMP_ADDR FROM EMPCHKTMP ORDER BY EMP_ST_ADDR ; EMP_ST_ADDR------------------------------BAN TIAN BULONG STREETFAN YU SESSIONHU BEI DIER SHIFAN XUEYUANMING ZHU YUANQING SHAN GANG DU HUA YUANTIAN MEN DUN NEARBYYANG MEI VILLAGE 7 rows selected

We can see that EMP_ST_ADDR is used as the table item in the display mode, 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.

INTERSECT: As the name implies, it is to take the intersection, that is, take two or more tables with the same content of the table items. For example:

SQL> SELECT EMP_ST_ADDR FROM EMPCHK INTERSECT SELECT EMP_ST_ADDR FROM EMPCHKTMP ; EMP_ST_ADDR------------------------------BAN TIAN BULONG STREETHU BEI DIER SHIFAN XUEYUAN

MINUS: As the name implies, it takes a subset, that is, the first table has a table item while the second table does not. For example:

SQL> SELECT EMP_ST_ADDR FROM EMPCHK MINUS SELECT EMP_ST_ADDR FROM EMPCHKTMP; EMP_ST_ADDR------------------------------FAN YU SESSIONQING SHAN GANG DU HUA YUAN

You can see the field:

FAN YU SESSIONQING SHAN GANG DU HUA YUAN

In the second table, EMPCHKTMP does not exist.

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> 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  100004    LI WEI          197****436  

A combined query can combine two or more tables. For example, the following query is performed based on the name field:

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 JIECAI FANG JIECAI FANG JIEDING LINGLI WEILI WEILU JING HUANLU JING HUANWU JING XINWU JING XINWU JING XINZOU 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> 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

We can see that the name field zou qi lei only exists in the EMPCHK and does not exist in the EMPCHKTMP and EMPCHKSUB tables.