Using FULL OUTER JOIN and/or UNION to combine tables with like fields

Question

I'm trying to create a view of two tables that have many fields in common.

The fields that exist in both tables should exist under one field in the new view. I've found some posts here and another I've lost track of that suggested using UNION and FULL OUTER JOIN to do what I want to do, but I'm quite new to this and the answers were a bit confusing. I think I need to select all fields from data and elongthat don't collide, then select the rest from a UNION ALL statement that combines all fields that the tables have in common, but how does the FULL OUTER JOIN come into play?

I think the FULL OUTER JOIN is needed to keep the number of rows in the view the same as the sum of the number of rows from each table, but I'm not quite sure.

Maybe this is my solution? I'm not sure how a CROSS JOIN will work with like fields though.

For those of you concerned with how I'm joining these, I'm trying to take each row from both tables and put them in a new view. My issues result from duplicate fields (c2, footnote1, footnote2, etc). I want them to remain as c2, footnote1, footnote2, but they keep recieving generated aliases. Everything that is a c2 is a c2, no mater which sheet it is from.

Edit: Thanks to the answers below, this is what I'm writing now, and it seems to be what I want. It is taking forever though because of the number of fields; is there a better way to do this? Something more automatic?

Edit2: This is what the complete query looks like, just in case it helps someone in the future. I hope there is a more automatic way to do this, because that took some time. Thank you to all who helped!

SELECT       ID, tableNum, UTC, colNum, c1, c2, null AS c3, c4, colRow, footnote1, footnote2, footnote3, footnote4, 
                                         property, orientation, specimenDesign, exposTempMinF AS exposTempFMin,   exposTempMaxF AS exposTempFMax, 
                                         exposTempRTMinF AS exposTempFRTMin,   exposTempRTMaxF AS exposTempFRTMax, designValueRoomTempF_A, 
                                         designValueRoomTempF_T99                                       , designValueRoomTempF_B                                     , 
                                         designValueRoomTempF_S                                    , designValueRoomTempF_Typical, designValueRoomTempF_Estimate, 
                                         designValueRoomTempF_Units                                         , conversionFactor, null AS form,  null AS thickRangeInMin, null AS thickRangeInMax, 
                                         null AS widthRangeInMin, null AS widthRangeInMax, null AS areaInSqrMin, null AS areaInSqrMax, note
FROM         dbo.[DesignAllowables-Data] AS data 
UNION ALL
SELECT       ID, tableNum, UTC, colNum, null, c2, c3         , null, colRow, footnote1, footnote2, null         , null           , 
                                         property, orientation, specimenDesign, exposTempF_min AS exposTempFMin, exposTempF_max AS exposTempFMax, 
                                         exposTempRTF_min AS exposTempFRTMin, exposTempRTF_max AS exposTempFRTMax, designValueRTF_A AS designValueRoomTempF_A, 
                                         designValueRTF_T99 AS designValueRoomTempF_T99, designValueRTF_B AS designValueRoomTempF_B, 
                                         designValueRTF_S AS designValueRoomTempF_S, null                                                , null                                                , 
                                         designValueRTF_Units AS designValueRoomTempF_Units, conversionFactor, form           , thickRangeInMin              , thickRangeInMax, 
                                         widthRangeInMin           , widthRangeInMax              , areaInSqrMin         , areaInSqrMax             , note
From           dbo.[DesignAllowables-Elong-RA] AS elong

Answer 1

The question you linked to is not applicable to your question.

Here are the some forms of combining to try out:

Full Outer Join

SELECT 
COALESCE(T1.Col1,T2.Col1) As Col1,
COALESCE(T1.Col2,T2.Col2) As Col2
FROM 
T1
FULL OUTER JOIN
T2
ON T1.ID = T2.ID

Matches on ID to combine rows. ID must be unique in both tables or you'll get 'duplicate' rows

Union All

SELECT 
T.ID,
MAX(T.Col1) As Col1,
MAX(T.Col2) As Col2
FROM
(
SELECT Col1,Col2 FROM T1
UNION ALL
SELECT Col1,Col2 FROM T2
) T
GROUP BY T.ID

Effectively matches on ID

This will not introduce duplicates if ID is not unique. It will arbitrarily pick a combination of values

Here's how you just return all the combined rows and selectively combine columns. In this example, C1 only exists in table T1

SELECT C1,C2, C3 
FROM T1
UNION ALL
SELECT NULL, C2,C3 
FROM T2

The important question is: do you have a unique key on each table? Do you need to match on it?

Answer 2

A UNION ALL basically returns both tables, one after another. JOIN basically returns the columns of both tables. This is an oversimplification, but a union combines rows and a join combines columns.

If both tables.have pretty much the same columns, it sounds like you want a UNION.