What is the difference between outer join and union

Other tutorials in this category 1. Another Example for Outer Joins three tables 4. All Rights Reserved. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies.

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. 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. 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. Suppose you were asked to provide a list of all AdventureWorks 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. Combining rows eliminates duplicates.

To combine data into a single result use both joins and unions. They both go about this is different ways. Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL. Wonderful and perfect explanation. Both being data combining operations are used in different situations. JOIN is used when we want to combine attributes of two relations having at least one attribute in common.

UNION is used when we want to combine the tuples of the two relations that are present in the query. Your email address will not be published. The JOIN clause is applicable only when the two relations involved have at least one attribute common in both. On the other hands, the UNION is applicable when the two relations have the same number of attribute and the domains of corresponding attributes are same.

In JOIN, the resultant tuple has the larger size as it includes attributes from both the relation.