Joins and Unions can be used to join data from one or one 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.

Example of using a join to lookup an employee’s name

ELECT   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;

Suppose you were asked to provide a list of all AdventureWorks2013 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

Leave a Reply

Your email address will not be published. Required fields are marked *