sql - Union between nonmatching columns tables

Question

Suppose we have 3 tables as follows:

Table1: Column1, Column2, Column3

Table2: Column1, Column2, Columnxyz, Column3

Table3: Column1, Column2, Column3

I have a view with the following DDL:

CREATE VIEW [dbo].[View1]
  AS (
    SELECT 'Table1' as [Source], * FROM [Table1] 
    UNION
    SELECT 'Table2' as [Source], * FROM [Table2]
    UNION
    SELECT 'Table3' as [Source], * FROM [Table3]
  );

That view works flawlessly assuming all tables have the same columns.

however, in the example above, with table2 having additional column: columnxyz What would I need to change in the view union? In other words, do I have to create Table1 and Table3 with this additional column as well, or can we circumvent this by somehow dynamically appending this extra column in the view to the other tables missing it and filling it with "None" or "N/A" as default data (since Table1 and table2 don't originally have this columnxyz so there's really no data anyways)?

question from:https://stackoverflow.com/questions/65838207/union-between-nonmatching-columns-tables

Answer 1

Select a constant from the tables without the additional column, exactly the same as you are already doing for [Source]. However you will have to list the columns explicitly since the additional column is not first or last. As a best practice you should always list your columns anyway.

CREATE VIEW [dbo].[View1]
AS (
  SELECT 'Table1' as [Source], Column1, Column2, 'N/A' AS ColumnXYZ, Column3
  FROM [Table1] 
  UNION ALL
  SELECT 'Table2' as [Source], Column1, Column2, ColumnXYZ, Column3
  FROM [Table2]
  UNION ALL
  SELECT 'Table3' as [Source], Column1, Column2, 'N/A' AS ColumnXYZ, Column3
  FROM [Table3]
);

Note, I've used UNION ALL because UNION will attempt to-duplicate your data, which normally you don't want, and it suffers a performance penalty for doing so. Personally I always handle an de-duplication/grouping in other ways.