An SQL JOIN and UNION both are used to combine the data from two or more tables having some relation. However, there is a difference between the JOIN and the UNION clause. In this tutorial, we will understand what are the similarities between them, the differences and when to use which clause. So, let’s get started!
Also read: Difference between JOIN and Multiple Tables in FROM
Introduction
As said earlier, both the JOIN and UNION clauses are used to combine the data from the multiple tables. However, the method of combining the data is different for these clauses.
You need to understand how they combine the data of the tables before using it in the query.
Let’s see detailed information on both clauses.
Difference Between JOIN and UNION
Now let’s see some important differences between JOIN and UNION quickly.
| JOIN | UNION |
|---|---|
| JOIN combines the data from multiple tables based on the relation between them. | Union combines the result of two or more select queries. |
| JOIN need not the tables to have the same number of columns and of the same data type. | UNION must need the result set to have similar numbers of columns and of the same data type. |
| It combines the two tables into new columns. | It combines the two result-set into new rows. |
| May or may not return distinct rows. The use of other functions is needed to remove the duplication. | UNION removes the duplicate rows. To keep the duplicate records, you can use the UNION ALL. |
What is JOIN and How it Works
When you use the JOIN to combine the data from two or multiple tables, the result will consist of new columns. Also, there must be a relation between the tables before using the JOIN. For example, the student table consists of the student id which is further used in the marks table to store the marks of each student. Here, the student id in both tables shows the relation between them.
Note that, if the first table consists of two columns and the second table consists of three columns, then the result might contain more than five columns as well. If you use some aggregate function or add a new column by some calculation or concatenation, the new column will appear in the result.
SQL provides us with multiple types of JOINs- INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN. Depending on the desired output, we can make use of the different joins.
Let’s take an example.
We have two tables- students and marks.
Here, the roll_number is a primary key in the “students” table and the foreign key in the “student_marks” table. Therefore, we can use JOIN to display the “students” names and their marks by combining the tables.
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s JOIN student_marks m
ON s.roll_number=m.roll_number; Code language: SQL (Structured Query Language) (sql)
As you can see, the columns are combined in the output.
Now let’s talk about the UNION.
What is UNION and How it Works
The UNION combines the results of multiple queries. This means, there are multiple select statements when using the UNION clause unlike the JOIN statement, where only one select statement is present.
When using the UNION, you have to make sure to follow rules to work it perfectly-
- The number of columns must be the same in both tables/results of both queries/both select statements.
- Every column must have the same data type.
The main difference between the JOIN and the UNION is that the JOIN works on the columns, whereas the UNION works on the rows.
That means the UNION gives the result by combining the multiple select statements having the same column names and the datatype.
Let’s take an example here.
We have the following tables- boys and girls.
Now, we will use the UNION clause to get the list of all names from both tables.
SELECT name FROM boys
UNION
SELECT name FROM girls;Code language: SQL (Structured Query Language) (sql)If you note that, the “boys” table contains the name ‘john’ twice. Therefore, the UNION clause will return that only once as it eliminates duplicate records.
Note that, this is because we are only selecting the name column. If we also select the id and the age column, the records become unique because each john has different roll numbers.
As you can see, we have got the ‘john’ name only once. If you want all the duplicate records as well then you can use the UNION ALL clause.
SELECT name FROM boys
UNION ALL
SELECT name FROM girls;Code language: SQL (Structured Query Language) (sql)
As you can see now, we have got the john name twice.
We can use the full join as well to bring the above result. However, MySQL doesn’t support the FULL join. In this case, the UNION is used to do the trick by getting the union of the left join and the right join of the tables.
Conclusion
In this tutorial, we have learned what is JOIN and UNION and how they work, what are similarities and differences in them and some examples. I hope you are clear now about the JOINs and the UNION. Now you can decide for yourself when to use the JOIN and UNION. Let’s not forget to share it with your friends also, happy learning!