What Is the Difference Between a Join and a UNION?

Use joins and UNIONS 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 are sure to practice using the examples!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.

What Is the Difference Between a Join and UNION?

There are several options you can use to combine data from multiple data. One of those option is to decide whether to use Joins or Unions.  But before you look into choosing one over the other, you need to understand how they combine data.

In simple terms, joins combine data into new columns.  The query uses a “join condition” to match column together to form new rows. The new rows consist of column values from both tables.

Unions combine data into new rows.  Here the union takes the result as rows and appends them together row by row.

Join – Visual Depiction

Here is a visual depiction of a join.  Notice we combine columns from Table A and B to create the result.

Each row in the result contains columns from BOTH table A and B.  Typically one or more columns are compared. Column are then combined when the comparison is true.

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 lookup column values by using the primary key in another.

Union – Visual Depiction

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.

Use UNION when you need to combine row from two different queries together.

A use case may be that you have two tables:  Teachers and Students.  You would like to create a master list of names and birthdays 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 a slightly deeper look into both.

Read More: SQL Union, Intersect, and Except – The Ultimate Guide >>

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

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 contain 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 unite two tables 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.

Combining rows eliminates duplicates.  If you want to keep all rows from both select statement’s results use the ALL keyword

Conclusion

To combine data into a single result use both joins and unions.  They both go about this is different ways.  Difference tables’ columns are combine using a join. The union combines different tables’ rows.