CODEWITHSUNDEEP
sql
Heisenberg
Heisenberg

Reputation: 8816

SQL UNION doesn't check for matching column names -- is it up to me?

I'm very surprised that UNION simply concatenates the rows without checking if the column names match.

For example,

SELECT 
    1 AS bar,
    2 AS foo

UNION ALL

SELECT 
    10 AS foo,
    20 AS bar

produces

bar foo
1   2
10  20

whereas I would expect SQL to complain that the first column is not of the same name across two tables.

Is this the intended behavior of SQL? If so, it seems like a huge opportunity for semantic error to me. What are ways I can use to guard against making this mistake? For large UNION with lots of columns, how to ensure that the columns line up in the same order other than manually checking it?

Upvotes: 11

Views: 11846

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Column names of the result are determined by the uppermost part of a union query, thereafter all subsequent column names are ignored..

However, the number of columns returned by all parts of the union must be equal, and each column must also "be of compatible" data types (e.g. decimal and integer in the same column are "compatible").

Like the column names the data type of each column is determined by the uppermost part of a union query. On occasion it can be useful to cast to a wanted data type. e.g.

SELECT 1 AS foo, CAST(2 AS DECIMAL(12,4)) AS bar
UNION ALL        
SELECT  10 , 20

or 

SELECT 1 AS foo, CAST(NULL AS DECIMAL(12,4)) AS bar
UNION ALL        
SELECT  10 , 20

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

For UNION does not matter what is your your two tables column name. The UNION operator is used to combine the result-set of two or more SELECT statements.

  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types

  • The columns in each SELECT statement must also be in the same order

    SELECT 1 AS bar, 2 AS foo   -- here number of column in both table is same and data type also same so its fulfill condition on union       
UNION ALL        
SELECT  10 AS foo, 20 AS bar

even if if you write below sql it will work fine causes here column data type order and number of column is same

SELECT 1 AS bar, 2 AS foo
UNION ALL        
SELECT  10 , 20

so it does not throw any error

Upvotes: 5

Related Questions