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