What Is the Difference Between a Join and UNION?

Joins and Unions can be used to combine data from one or more tables.  The difference lies in how the data is combined.

In simple terms, joins combine data into new columns.  If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table’s column in the same row.

Unions combine data into new rows.  If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table in another set.  The rows are in the same result.

Here is a visual depiction of a join.  Table A and B’s columns are combined into a single result.

Joins Combine Columns

Each row in the result contains columns from BOTH table A and B.  Rows are created when columns from one table match columns from another.  This match is called the join condition.

This makes joins really great for looking up values and including them in results.  This is usually the result of denormalizing (reversing normalization) and involves using the foreign key in one table to look up column values by using the primary key in another.

Now compare the above depiction with that of a union.  In a union each row within the result is from one table OR the other.  In a union, columns aren’t combined to create results, rows are combined.

Unions Combine Rows

Unions are typically used where you have two results whose rows you want to include in the same result.  A use case may be that you have two tables:  Teachers and Students.  You would like to create a master list of name and birth days sorted by date.

To do this you can use a union to first combine the rows into a single result and then sort them.

Let’s now take slightly deeper look into both.

Combining Data with a Join

In this section well look at the inner join.  It is one of the most common forms of join and is used when you need to match rows from two tables.  Rows that match remain in the result, those that don’t are rejected.

Below is an example of a simple select statement with an INNER JOIN clause.

SELECT columnlist
FROM   maintable
       INNER JOIN 
       secondtable ON join condition

Here is an example of using a join to lookup an employee’s name:

SELECT   <span style="color: #0000ff;">Employee.NationalIDNumber,
         Person.FirstName,
         Person.LastName,
         Employee.JobTitle</span>
FROM     HumanResources.Employee
         INNER JOIN
         Person.Person
         ON <span style="color: #ff0000;">HumanResources.Employee.BusinessEntityID = person.BusinessEntityID</span>
ORDER BY person.LastName;

Here are the results

INNER JOIN Results

You can learn more about INNER JOINS here, but for now here are two things I want to point out.

First, notice the join condition, see how we are matching BusinessEntityID from both tables.

Second, check out that the results contains columns from both tables.

Combining Data with a UNION

Let’s take a closer look at the UNION statement.  In SQL the UNION statement looks like

SELECT columnlist
FROM   table1
UNION
SELECT columnlist
FROM   table2

Suppose you were asked to provide a list of all AdventureWorks2012 product categories and subcategories.  To do this you could write two separate queries and provide two separate results, such as two spreadsheets, or you could use the UNION clause to deliver one result.

SELECT C.Name
FROM   Production.ProductCategory AS C
UNION ALL
SELECT S.Name
FROM   Production.ProductSubcategory AS S

In order to union two table there are a couple of requirements:

1. The number of columns must be the same for both select statements.
2. The columns, in order, must be of the same data type.

When rows are combined duplicate rows are eliminated.  If you want to keep all rows from both select statement’s results use the ALL keyword

Conclusion

Both joins and unions can be used to combine data from one or more tables into a single results.  They both go about this is different ways.  Whereas a join is used to combine columns from different tables, the union is used to combine rows.

The post What Is the Difference Between a Join and a UNION? appeared first on Essential SQL.