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.
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.
Combining rows eliminates duplicates. If you want to keep all rows from both select statement’s results use the ALL keyword
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.