UNION，EXCEPT, CASE和相关构造

UNION和相关构造解析

UNION分为UNION和UNION ALL。UNION将数据聚合并进行去重处理，UNION ALL只聚合数据，不做去重处理。

示例：

-- 创建表
m_db=# CREATE TABLE test_union1 (union_col1 INT);
m_db=# CREATE TABLE test_union2 (union_col2 CHAR(5));

-- 插入数据
m_db=# INSERT INTO test_union1 VALUES (123);
m_db=# INSERT INTO test_union2 VALUES ('123');

-- union用法
m_db=# SELECT union_col1 FROM test_union1 UNION SELECT union_col2 FROM test_union2;
 union_col1 
------------
 123
(1 row)

-- union all用法
m_db=# SELECT union_col1 FROM test_union1 UNION ALL SELECT union_col2 FROM test_union2;
 union_col1 
------------
 123
 123
(2 rows)

-- 非列值UNION应用
m_db=# SELECT '234' FROM test_union1 UNION SELECT '234';
 ?column? 
----------
 234
(1 row)

EXCEPT和相关构造解析

EXCEPT分为EXCEPT、EXCEPT ALL和EXCEPT DISTINCT。EXCEPT和EXCEPT DISTINCT会在第一个查询结果中去除两个查询结果的交集并对结果进行去重处理，EXCEPT ALL不做去重处理。

示例：

-- 创建表
m_db=# CREATE TABLE data_types (
  col_int1 TINYINT,
  col_int2 SMALLINT,
  col_int4 INT,
  col_int8 BIGINT,
  col_uint1 TINYINT UNSIGNED, 
  col_uint2 SMALLINT UNSIGNED,
  col_uint4 INT UNSIGNED,
  col_uint8 BIGINT UNSIGNED,
  col_float FLOAT
);

-- 插入数据
INSERT INTO data_types
VALUES
  (
   1,
   2,
   3,
   4,
   1,
   2,
   3,
   4,
   1.1
);

-- except用法
m_db=# SELECT col_int4 FROM data_types EXCEPT SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- except all用法
m_db=# SELECT col_int4 FROM data_types EXCEPT ALL SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT ALL SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- except distinct用法
m_db=# SELECT col_int4 FROM data_types EXCEPT DISTINCT SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT DISTINCT SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- 非列值except应用
m_db=# SELECT col_int4 FROM data_types EXCEPT SELECT '4';
 col_int4
----------
 3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT SELECT '4');
 pg_typeof
-----------
 varchar
(1 row)

-- 删除表
m_db=# DROP TABLE data_types;

CASE和相关构造解析

CASE语法一共有两种表达方式，两种方式功能含义相同：

CASE WHEN col = A THEN ... ELSE ...

CASE col WHEN A THEN ... ELSE ...

示例：

-- 创建表
m_db=# CREATE TABLE test_case1 (case_col1 INT);

-- 插入数据
m_db=# INSERT INTO test_case1 VALUES (1), (23), (45), (1);

-- case用法
m_db=# SELECT CASE WHEN case_col1 = 1 THEN 'a' ELSE 'b' END FROM test_case1;
 case 
------
 a
 b
 b
 a
(4 rows)

m_db=# SELECT CASE case_col1 WHEN 1 THEN 'a' ELSE 'b' END FROM test_case1;
 case 
------
 a
 b
 b
 a
(4 rows)

在精度传递场景下，使用CASE WHEN语句时，会进行类型转换和精度重新计算，导致最终的输出结果与CASE子句对比会出现末尾多零场景或末尾少零场景：

• 补零场景：CASE节点会根据CASE子句的精度计算CASE节点精度，当THEN子句的精度比CASE节点的精度小时，会在CASE节点末尾补零。在CASE WHEN语句中出现的用户变量，用户变量将使用所保存类型的默认精度作为用户变量的精度。
• 末尾少零场景：多层CASE WHEN嵌套时，内层CASE执行类型转换之后，只保留内层CASE的精度，外层CASE无法得到THEN子句的精度信息，因此外层CASE会根据内层CASE的精度计算的精度进行类型转换。当外层CASE类型转换时内层CASE精度比THEN子句少，会出现末尾少零场景。

示例：

-- 末尾补零场景
m_db=# SELECT 15.6 AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE  23.578 END AS result;
 result 
--------
 15.600
(1 row)

m_db=# SELECT  greatest(12, 3.4, 15.6) AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result;
 result 
--------
 15.600
(1 row)

-- 末尾零省略场景
m_db=# CREATE TABLE t1 AS SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
INSERT 0 1
m_db=# desc t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | double(8,7) | YES  |     |         | 
(1 row)

m_db=# SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
   result   
------------
 -0.0000000
(1 row)

m_db=# cREATE TABLE t1 AS SELECT (case when 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result;
INSERT 0 1
m_db=# DESC t1;
 Field  |  Type  | Null | Key | Default | Extra 
--------+--------+------+-----+---------+-------
 result | double | YES  |     |         | 
(1 row)

m_db=# SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11E3/'22.2' END) AS result;
 result 
--------
     -0
(1 row)

m_db=# DROP TABLE t1;
DROP TABLE
m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
INSERT 0 1
m_db=# DESC t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | varchar(23) | YES  |     |         | 
(1 row)

m_db=# SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
 result 
--------
 -0
(1 row)