Appending

We wish to combine multiple tables into a single table vertically where rows are appended to each other. Both tables must have the same number of columns. This is referred to as Union.

This section is organized as follows:

  • Union All where we cover how to append two or more tables vertically. i.e. concatenate rows while aligning columns without removing duplicat rows.
  • Union Distinct where we cover how to append two or more tables vertically. i.e. concatenate rows while aligning columns and removing any duplicate rows.

Union All

We wish to append two tables together vertically where rows are appended to each other and columns in both tables are aligned based on position.

In this example, we wish to append table_1 and table_2 and return a single table with all rows and columns from both tables.

SELECT *
FROM table_1
UNION ALL
SELECT *
FROM table_2;

Here is how this works:

  • We use the UNION operator to append rows from table_1 and table_2 vertically.
  • We use UNION ALL to keep all rows from both tables. If a given row x appears m times in table_1 and n times in table_2, then x appears exactly m + n times in the output.
  • Output columns will have the names of columns in table_1. table_2 columns names are ignored and do not need to have the same names as table_1, but columns in the same position must have the same data type.
  • The output table will contain all the rows from both table_1 and table_2, appended one after the other and aligned based on column positions.

Extension: More than 2 tables

We wish to append 3 or more tables vertically.

In this example, we wish to append table_1, table_2, and table_3 and return a single table with all rows and columns from both tables.

SELECT *
FROM table_1
UNION ALL
SELECT *
FROM table_2
UNION ALL
SELECT *
FROM table_3;

Here is how this works:

This works similarly to the solution above with an additional UNION ALL operation.

Extension: Table Identifier

We wish to create a new column of identifiers to link each row to its original table.

In this example, we wish to append table_1 and table_2 vertically and add a new column named source to the output table.

SELECT *,
       'table_1' AS source
FROM table_1
UNION ALL
SELECT *,
       'table_2' AS source
FROM table_2;

Here is how this works:

This works similarly to the solution above with an additional column source that we add after SELECT *.

Union Distinct

We wish to append two tables together vertically where rows are appended to each other and columns in both tables are aligned based on position. We only keep one copy of each row if it has any duplicates.

In this example, we wish to append table_1 and table_2 and return a single table with all rows and columns from both tables and keep only one instance per row.

SELECT *
FROM table_1
UNION DISTINCT
SELECT *
FROM table_2;

Here is how this works:

  • This works similarly to the Union All above except we use UNION DISTINCT operation.
  • If a given row x appears m times in table_1 and n times in table_2, then x appears only one time in the output.
SQL
I/O