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 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. 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?
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.
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 lookup 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 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 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.
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:
- The number of columns must be the same for both select statements.
- 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
Both joins and unions can be used to combine data from one or more tables into a single result. 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.