PostgreSQL----union&&intersect&&except

Multiple SELECT statements can be set using Union,intersect and except, where union is used to find unions, intersect for intersection, and except for differential sets. Use the following

Query1 UNION Query2

Query1 INTERSECT Query2

Query1 EXCEPT Query2

Where the number of fields in the SELECT clause of Query1 and Query2 must be the same, and the corresponding data types must be the same (if the int and bigint results take the Bigint,varchar (5) and the varchar (10) result to varchar (10)), The field name can be different, but the final result is returned as the field name in the first select clause.

Test table:

Test=#Create TableTbl_test1 (Test (# aint, Test (# bvarchar(Ten), Test (# Cvarchar(5) test (#);CREATE TABLETest=#Create TableTbl_test2 (Test (# eint, Test (# fvarchar(Ten), Test (# Gvarchar( +) test (#);CREATE TABLETest=#Insert  intoTbl_test1 (A,B,C)Values(1,'HA',' A'),(2,'ha','543');INSERT 0 2Test=#Insert  intoTbl_test2 (E,F,G)Values(1,'HA','DH'),(3,'HK','76sskjhk');INSERT 0 2

I. UNION

Two queries the result of using Union is represented by a picture as follows:

Union defaults to deduplication, which means that duplicate data returns only one row, and if you need to keep duplicate data all rows can use union ALL.

Example 1.

 test=  # select  A, b from  tbl_test1 select  e,f Span style= "color: #0000ff;" >from   Tbl_test2; a  |   b  -- -+---- 3  |   HK  1  |   HA  2  |   ha ( 3  rows) 

Example 2.

Test=#SelectA, b fromTbl_test1Union  All SelectE,f fromTbl_test2; a|b---+---- 1 |HA2 |ha1 |HA3 |HK (4Rows

Example 3.

Test=#SelectA,c fromTbl_test1Union  All SelectE,g fromTbl_test2; a|C---+---------- 1 |  A 2 | 543 1 |DH3 |76SSKJHK (4Rows

two. INTERSECT

Two queries the result of using Intersect for intersection uses a picture to represent the following:

Example 1.

Test=Selectfromintersectselectfrom| b   -- -+---- 1 | HA (1 Row)

three. EXCEPT

Two queries use EXCEPT (A EXCEPT B) to find the result of the difference set:

Example 1.

Test=Selectfromexceptselectfrom| b   -- -+---- 2 | ha (1 Row)

PostgreSQL----union&&intersect&&except