Difference Between JOIN and UNION in SQL

1. Introduction

We use JOIN and UNION in SQL as fundamental operations to combine data from multiple tables. While both serve to merge data, they function differently and are used in distinct scenarios. Understanding the differences between JOIN and UNION is important for database management and query optimization.

In this tutorial, we’ll explore the definitions and types of JOIN and UNION in SQL. Also, we’ll provide practical examples using the Baeldung University database scheme. In particular, we’ll use the Department and Faculty tables for illustration in this tutorial. Finally, we’ll conclude with the differences between both.

Notably, we perform all operations in the Postgres database management system (DBMS). However, the queries used are also compatible with other SQL systems, such as MySQL and SQL Server.

2. JOIN

A JOIN operation combines rows from two or more tables based on a related column between them. We use JOIN to retrieve data spread across multiple tables.

There are several types of JOINs, each serving a different purpose and use case:

• INNER JOIN: this JOIN returns only the rows with matching values in both tables
• LEFT JOIN (or LEFT OUTER JOIN): it returns all rows from the left table and the matched rows from the right table. Furthermore, if no match is found, NULL values are returned for columns from the right table
• RIGHT JOIN (or RIGHT OUTER JOIN): this operation is the opposite of LEFT JOIN. It returns all rows from the right table and the matched rows from the left table. Additionally, if no match is found, NULL values are returned for columns from the left table
• FULL JOIN  (or FULL OUTER JOIN): it returns all rows from both tables. When there’s a match between the tables, the joined row contains values from both tables. Additionally, if there’s no match, the result includes NULL values for the columns from the table without a match.

To demonstrate a JOIN, we can retrieve a list of faculty members along with their respective department names:

SELECT Faculty.name AS FacultyName, Department.name AS DepartmentName
FROM Faculty
INNER JOIN Department ON Faculty.department_id = Department.id;
+-----------------+--------------------------------+
| FacultyName     | DepartmentName                 |
|-----------------+--------------------------------|
| Anubha Gupta    | Electronics and Communications |
| Anubha Gupta    | Electronics and Communications |
| Anubha Gupta    | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Nando de Fretas | Electronics and Communications |
| Nando de Fretas | Electronics and Communications |
...

This SQL query used INNER JOIN to combine rows from both tables where the department_id in the Faculty table matches the id in the Department table. The query includes only the faculty members who have a department association.

3. UNION

A UNION operation combines the result sets of two or more SELECT statements. In particular, the combined result set includes all unique rows from the SELECT statements. Furthermore, UNION removes duplicates by default. However, if duplicates should be retained, the UNION ALL keyword can be used.

Let’s use UNION to create a list of names of all departments and faculty members:

SELECT name AS Name FROM Department
UNION
SELECT name AS Name FROM Faculty;
+--------------------------------+
| Name                           |
|--------------------------------|
| Mathematics                    |
| Rory Ross                      |
| Ron Parry                      |
| Badrinath Ho                   |
| Casper Jones                   |
| Risa Sodi                      |
| Score Koli                     |
| Ajit Singh                     |
| Karen Walter                   |
| Michael Albin                  |
| Steve Joseph                   |
| Steven Joe                     |
...

This query used UNION to combine the names from both tables, showing unique names.

4. Differences

Let’s look at the key differences between JOIN and UNION:

5. Conclusion

In this article, we’ve explored JOIN and UNION for combining data from multiple tables in SQL. JOIN merges rows based on related columns, whereas UNION combines result sets from multiple SELECT statements. Additionally, we’ve understood their differences and practical use cases.