Union - Animated

3
min read

Union All

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.

CODE:https://gist.github.com/fronofro/fa1ce1fa22a345f3ed23d8e1b5851070.js

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.

CODE:https://gist.github.com/fronofro/8d2fb92e334620a3972f06d6096efe0b.js

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.

CODE:https://gist.github.com/fronofro/47af0fc2a6c2e86388eb85e3fd640cf8.js

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

CODE:https://gist.github.com/fronofro/a7d435d53f306572982995772282e555.js

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.

CODE:https://gist.github.com/fronofro/1e3a2059a97e5a818b7614d2953f255e.js

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?