Is there a UNION equivalent to FULL OUTER JOIN in T-SQL that will union dissimilar column sets?

Question

I am trying to accomplish the following:

SELECT col1, col2 FROM table1
UNION
SELECT col2, col3 FROM table2

With the result:

col1, col2, col3
1   , 1   , NULL
NULL, 1   , 1

The union of the columns and the rows is returned. You could think of it as the UNION equivalent of a FULL OUTER JOIN.

The simple answer to this question is:

SELECT col1, col2, NULL AS col3 FROM table1
UNION
SELECT NULL AS col1, col2, col3 FROM table2

This, however, is difficult to do with dynamic T-SQL and dbo.sp_executesql, and the resulting query could be too long if a large number of UNIONS is involved.

The best solution I could come up with was to create a temporary table with all the possible columns and insert each select into the temporary table in turn, like this:

CREATE TABLE #temp ( col1 int, col2 int, col3 int )
INSERT INTO #temp ( col1, col2 ) SELECT col1, col2 FROM table1
INSERT INTO #temp ( col2, col3 ) SELECT col2, col3 FROM table2

But this requires knowing ahead of time what the column names are. My particular scenario and this question assumes that if the column names match the types match as well. In fact, the columns that I am trying to manipulate are all of the same type.

Is there a simpler way to do this?

Thanks!

Answer 1

After a quick try, the closest I can get in T-SQL without jumping through loads of flaming hoops of hackiness is using a FULL OUTER JOIN with a false condition... e.g.

DECLARE @TableA TABLE (Col1 INTEGER IDENTITY(1,1), Col2 INTEGER)
DECLARE @TableB TABLE (Col2 INTEGER IDENTITY(1,1), Col3 INTEGER)

INSERT @TableA VALUES (1)
INSERT @TableA VALUES (11)
INSERT @TableB VALUES (2)
INSERT @TableB VALUES (222)

SELECT *
FROM @TableA a
    FULL OUTER JOIN @TableB b ON 0 = 1

You'll see from the results though, that Col2 is included twice in the resultset...it's not combined into one.

My suggestion would be to handle this in your code that sits over this, instead of trying it in T-SQL - return multiple resultsets to your calling code and have that deal with the different schemas. If you really need it as one resultset, could that calling code not combine the results into one?

Answer 2

My interpretation of your question is that you want to be able to support an ad-hoc query, given the talk about dynamic SQL & column names & data types...

This query will give you a list of columns & their data types for a specific table:

SELECT @columnName = c.name AS columnName,
       @columnDataType = ty.name AS columnDataType
  FROM SYS.TABLES ta
  JOIN SYS.COLUMNS c ON c.object_id = ta.object_id
  JOIN SYS.TYPES ty ON ty.user_type_id = c.user_type_id
 WHERE ta.name = '[your_table_name]'

This populates the @columnName & @columnDataType variables so you can use them elsewhere.

You'll need to supply the column name, and table name that the column resides in at a minimum to get meaningful data for a single column. After that, it's a matter of constructing the logic you want for what to do in order to create the query you want.

A simpler approach would be to determine what is the query needed for the majority of situations.
The sad part is that when you ask, the answer is usually "everything". For that response, I'd lay things out that in order to support everything you need an amount of currency & time. Stress that it is cheaper & faster to break up the requirement into manageable portions - this will also allow you to be able to get feedback. It's possible that no one will ever use a small subset of the functionality, or it's not what the user needs at all. Learn to manage client expectations.

Answer 3

Will this help(joining upon some dummy column) in giving some insight?

declare @t1 table(col1 varchar(10),col2 varchar(10))
declare @t2 table(col2 varchar(10),col3 varchar(10))
insert into @t1 select '1','1' union all select '10','1'
insert into @t2 select '1',null union all select '10','1'

;with cte1 as(select ROW_NUMBER() over(order by getdate()) rn1, t1.* from @t1 t1)
,cte2 as(select ROW_NUMBER() over(order by getdate()) rn2, t2.* from @t2 t2)
select c1.col1,c1.col2,c2.col3
from cte1 c1 
full outer join cte2 c2
on c1.rn1 = c2.rn2

Output:

col1    col2 col3
1   1 NULL
10  1 1

Answer 4

I threw in the towel on this one and wrote a bunch of nasty dynamic T-SQL and executed it with sp_executesql. Thanks for the help!