SQL - default columns

Question

I have the following SQL statement

SELECT table1.col1, table2.col2 FROM table1, table2 WHERE table1.t_id = table2.t_id
UNION
SELECT table1.col1 FROM table1 WHERE table1.col4 = null

Problem is, I this syntax is invalid because the 2nd statement doesn't include the same # of columns as the 1st statement.

I can't include table2.col2 in the second statement because I don't need it.

Is there anyway for me to include a default second column in the 2nd statement so that I can get this query to work?

Answer 1

Yes, just use a constant such as 1 or ''.

SELECT table1.col1, table2.col2 FROM table1, table2 WHERE table1.t_id = table2.t_id
UNION
SELECT table1.col1, '' as col2 FROM table1 WHERE table1.col4 = null

Answer 2

Maybe this is what you are looking for? You don't have to pull values out of the table... you can always create them out of thin air. :)

SELECT table1.col1, table2.col2 FROM table1, table2 WHERE table1.t_id = table2.t_id
UNION
SELECT table1.col1, 'whatever' FROM table1 WHERE table1.col4 is null

Answer 3

Just use empty ('') quotes in your column list for columns you don't want in the second SQL statement.

Answer 4

I typically use NULL (which should coerce into any type):

SELECT table1.col1, table2.col2 FROM table1, table2 WHERE table1.t_id = table2.t_id
UNION
SELECT table1.col1, NULL AS col2 FROM table1 WHERE table1.col4 = null

Answer 5

When doing a union or union all the number of columns must match. You'll need to either hard code a value to put in that column, or query the sys.columns catalog view and get the default value for the column you are inserting into, and use that value there.