union all with queries that have a different number of columns

Question

I've run into a case where a sqlite query I'm expecting to return an error is actually succeeding and I was wondering if anyone could point out why this query is valid.

CREATE TABLE test_table(
  k INTEGER,
  v INTEGER
);

INSERT INTO test_table( k, v ) VALUES( 4, 5 );

SELECT * FROM(
  SELECT * FROM(
    SELECT k, v FROM test_table WHERE 1 = 0
  )
  UNION ALL
  SELECT * FROM(
    SELECT rowid, k, v FROM test_table
  )
)

sqlfiddle of above

I would think that unioning two selects which have a different number of columns would return an error. If I remove the outermost SELECT * then I receive the error I'm expecting: SELECTs to the left and right of UNION ALL do not have the same number of result columns.

Weird - and what does it return? NULLs for the missing column? — Michael Berkowski, Jul 28 '12 at 20:41
@Michael : It returns the rowid and k without any third column. — user1560123, Jul 28 '12 at 20:43
@Ludo : I would expect it to be invalid since a different number of columns are returned by the select statements being unioned. — user1560123, Jul 29 '12 at 13:15
Yes that makes sense, but can you point to the standard that provides a such requirement? — , Jul 29 '12 at 13:19

score 12 · Answer 1 · answered Jul 31 '12 at 15:28

The answer to this seems to be straightforward: Yes, this is a quirk. I'd like to demonstrate this with a short example. But beforehand, let's consult the documentation:

Two or more simple SELECT statements may be connected together to form a compound SELECT using the UNION, UNION ALL, INTERSECT or EXCEPT operator. In a compound SELECT, all the constituent SELECTs must return the same number of result columns.

So the documentations says very clearly that two SELECTs must provide the same number of columns. However, as you said, the outermost SELECT strangely avoids this 'limitation'.

Example 1

SELECT * FROM(
    SELECT k, v FROM test_table
  UNION ALL
    SELECT k, v,rowid FROM test_table
);

Result:

k|v
4|5
4|5

The third column rowid gets simply omitted, as pointed out in the comments.

Example 2

We are only switching the order of the two select statements.

 SELECT * FROM(
    SELECT k, v, rowid FROM test_table
  UNION ALL
     SELECT k, v FROM test_table
  );

Result

k|v|rowid
4|5|1
4|5|

Now, sqlite does not omit the column but add a null value.

Conclusion

This brings me to my conclusion, that sqlite simply handles the UNION ALL differently if it is processed as a subquery.

PS: If you are just using UNION it fails at any scenario.

score 10 · Answer 2 · edited Jul 04 '17 at 14:13

10

UNION ALL will return the results with null values in the extra columns.

A basic UNION will fail because UNION without the ALL has to have the same number of columns from both tables.

So:

SELECT column1, column2 FROM table a
UNION ALL 
SELECT column1, column2, column3 FROM table b

returns 3 columns with nulls in column 3.

and:

SELECT column1, column2 FROM table a
UNION 
SELECT column1, column2, column3 FROM table b

should fail because the number of columns do not match.

In conclusion you could add a blank column to the UNION so that you are selecting 3 columns from each table and it would still work.

EX:

SELECT column1, column2, '' AS column3 FROM table a
UNION  
SELECT column1, column2, column3 FROM table b

edited Jul 04 '17 at 14:13

brunoais

6,258
8
39
59

answered Aug 08 '12 at 03:17

Sam Rice

141
6

2

This answer does not appear to be an accurate description of UNION vs UNION ALL. Based on this https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all, UNION and UNION ALL are the same except that UNION also does a SELECT DISTINCT on the result. It has nothing to do with the columns – gunit Jan 22 '19 at 22:06
1

UNION ALL does *not* accept different column counts. – neydroydrec Oct 21 '20 at 08:04

score 2 · Answer 3 · answered Feb 27 '20 at 06:35

If your second query has less number of columns, you can do this:

select col1, col2, col3, col4, col5
from table A

union all

select col1, col2, col3, col4, NULL as col5, 
from table B

Instead of NULL, one can also use some string constant - 'KPI' as col5.

union all with queries that have a different number of columns

3 Answers3

Linked