union all with queries that have a different number of columns

user_1560123 0
Viewed 75854

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.

sqlite sql
3 Answers

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.

user_31875370
Related