4

What Is the Difference Between a Join and a UNION?

UnionAndJoin

Both Joins and UNIONS can be used to combine data from two or more tables.  Read this article to find out each command’s strengths and when to use them.

To get the most of this and our other lessons be sure to practice using the examples!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

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.

Use Joins to Combine Columns

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.

Use a union to combine rows

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   Employee.NationalIDNumber,
         Person.FirstName,
         Person.LastName,
         Employee.JobTitle
FROM     HumanResources.Employee
         INNER JOIN
         Person.Person
         ON HumanResources.Employee.BusinessEntityID = person.BusinessEntityID
ORDER BY person.LastName;

Here are the results

Results of an Inner Join

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.

 

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and an MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He is loves helping others learn SQL.

Click Here to Leave a Comment Below 4 comments