Union - Animated

Last modified: December 31, 2019

This is the fourth most common type of JOIN in SQL. Union does not attach the data from two tables to a single row. Union stacks two data sets on top of each other into a single table.

SELECT *
FROM facebook
UNION ALL
SELECT *
FROM linkedin

There are a few considerations you must make before performing a UNION. The number and order of columns and for both tables must be the same. The data types of the columns that are being combined must have the same data type. You can specify which column(s) you want to union, and in this example we will select only the Name column from both tables which are both text and so they can be unioned.

SELECT Name
FROM facebook
UNION ALL
SELECT Name
FROM linkedin

The two tables referenced and the unionized version of the tables

However if the columns selected do not have the same data type such as trying to Union the Name column of the facebook table and the # of connections column from the linkedin table.

SELECT Name
FROM facebook
UNION ALL
SELECT # of connections
FROM linkedin

This will trigger an error.

We would also get this error even if we have both columns selected from the tables but in different order

SELECT Name, # of friends
FROM facebook
UNION ALL
SELECT # of connections, Name
FROM linkedin

However if we put the columns we are selecting in order, then it will run the same as our first example query since this is the order the columns are in in their respective tables.

SELECT Name, # of friends
FROM facebook
UNION ALL
SELECT Name, # of connections
FROM linkedin

gif showing how the union runs through each table to build the result

Why use a UNION vs a LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER JOIN? To help understand, Let’s think about the different questions they are asking.

INNER join: How many friends and connections do my friends who are on both on Facebook and LinkedIn have?
LEFT join: How many friends and connections do my Facebook friends have? (Regardless of if they are on LinkedIn)
RIGHT join: How many friends and connections do my LinkedIn connections have? (Regardless of if they are on facebook)
FULL OUTER join: How many friends and connections do my Facebook friends or LinkedIn connections have?
UNION: How many friends do my Facebook friends have and how many connections do my LinkedIn connections have?

Written by: Matt David
Reviewed by: Dave Fowler

Created by