UNION, CASE, and Related Constructs

Type Resolution for UNION and Related Constructs

UNION is classified into UNION and UNION ALL. UNION aggregates data and deduplicates data. UNION ALL aggregates data but does not deduplicate data.

Example:

-- Create a table.
m_db=# CREATE TABLE test_union1 (union_col1 INT);
m_db=# CREATE TABLE test_union2 (union_col2 CHAR(5));

-- Insert data.
m_db=# INSERT INTO test_union1 VALUES (123);
m_db=# INSERT INTO test_union2 VALUES ('123');

-- Usage of UNION
m_db=# SELECT union_col1 FROM test_union1 UNION SELECT union_col2 FROM test_union2;
 union_col1 
------------
 123
(1 row)

-- Usage of 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 application of a non-column value
m_db=# SELECT '234' FROM test_union1 UNION SELECT '234';
 ?column? 
----------
 234
(1 row)

-- Drop the table.
m_db=# DROP TABLE test_union1;
m_db=# DROP TABLE test_union2;

Type Resolution for CASE and Related Constructs

The CASE syntax has two expression modes, which have the same functions and meanings.

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

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

Example:

-- Create a table.
m_db=# CREATE TABLE test_case1 (case_col1 INT);

-- Insert data.
m_db=# INSERT INTO test_case1 VALUES (1), (23), (45), (1);

-- Usage of 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)

-- Drop the table.
m_db=# DROP TABLE test_case1;