We have covered quite a few tutorials on JOIN and UNION. If you have studied all of them thoroughly, you probably already had a deep understanding about how to combine data from different tables. In this tutorial, we'll look at JOIN and UNION together to list the key differences between the two.

Both join and union combine data from one or more tables into a single result, but the syntax, format, and structure are quite different.

The key differences between JOIN and UNION are below.

1. JOIN combines data from one (self join) or more tables horizontally, whereas UNION combines data vertically.

   JOIN combines tables horizontally

   UNION combines tables vertically

2. JOIN combines columns and UNION combines rows.

3. JOIN combines data when the involved tables have common attributes for at least one column, whereas UNION combines data when the involved tables have (1) the same number of columns, and (2) the columns are compatible.

4. In real world database, JOINs are normally based on primary keys and foreign keys in join condition, whereas UNION does not have union condition except point 3 above.

5. FULL (OUTER) JOIN can potentially return very large result sets (Note: MySQL does not support FULL JOIN).

6. Variants of JOIN are inner join, left join, right join, full join, and cross join, whereas UNION's variants are UNION and UNION ALL. In not very strict terms, INTERCEPT and MINUS/EXCEPT could also be viewed as variants of UNION because they combine data vertically as well, just like what UNION does.

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to do MINUS/EXCEPT and INTERSECT in MySQL

9. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

10. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

11. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

12. Use Cross Join to Combine Data in MySQL

Back to Tutorial Index Page