Hello,
I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.
Thank you for your help!
UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:
Code Snippet
USE Northwind
GO
SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno TaquerÃa
Around the Horn
Berglunds snabbk?p
Bill Smith
...|||
Donnie:
The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:
The number of columns the same The datatypes of corresponding columns be the same|||Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".
declare @.t1 table(c1 int, c2 int)
declare @.t2 table(c3 int, c4 int)
insertinto @.t1 values(1, 2)
insertinto @.t2 values(3, 4)
select c1, c2 from @.t1
union all
select c3, c4 from @.t2
AMB
|||Are you certain that it is a UNION that you need to perform, and not a JOIN?
A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:
Code Snippet
Table 1 - Sample Data
Column1a Column2a Column3a
--
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
Table 2 - Sample Data
Column1b Column2b Column3b
--
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
Output
Column1a Column2a Column3a Column1b Column2b Column3b
--
1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1
2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2
3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3
SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b
A UNION will allow you to horizontally merge the data from both tables, like so:
Code Snippet
Table 1 - Sample Data
Column1a Column2a Column3a
--
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
Table 2 - Sample Data
Column1b Column2b Column3b
--
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
Output
Column1 Column2 Column3
-
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3
FROM Table1 t1
UNION ALL
SELECT t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table2 t2
Chris|||
Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?
Thanks for your help!
Yes, a full join should work for you.
SELECT a.Column1a, a.Column2a, a.Column3a,
b.Column1b, b.Column2b, b.Column3b
FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)
There should not be any duplicates in the result set.
