What's the Difference between JOIN and UNION in SQL?

The SQL JOIN clause is used in order to combine two or more tables based on common property and produce a result whereas the SQL UNION clause is used when there is a need to produce a conjunction or the combined result of two SELECT statements.

What is JOIN in SQL?

The SQL JOIN clause is used when we have to obtain results by combining two or more tables based on common property. This common property is a column related between them i.e. generally the column that is acting as a foreign key in one of the tables.

There are mainly four types of join in SQL:

INNER Join
LEFT Join
RIGHT Join
OUTER Join

The following diagram describes these joins in SQL:

join-in-sql

What is UNION in SQL?

The UNION clause in SQL is used when we have to display the result by combining multiple SELECT statements in our SQL query. The UNION clause in SQL returns results without returning any duplicate rows.

Example of JOIN and UNION in SQL

In this section, we will go through examples of the JOIN clause and UNION clause in SQL.

JOIN in SQL

Suppose we are given two tables Student and TechStack. The Student table contains three column student_id, student_name, and city and the table TechStack also contains three columns student_name, city and technology.

example-of-join-in-sql

Now suppose we have to display the student_id, student_name, city, and technology. Since all of them are not present in a single table thus we will have to join both tables to produce the result.

Code:

Output:

The output table will be like the following:

output-join-in-sql

UNION in SQL

Suppose we are given two tables Student1 and Student2. Both table contains four columns student_id, student_name, and subject and score.

table-for-union-in-sql

Now suppose, we have to display the student_name and subject for every student. Thus in this case we will display the union of the student_name and subject for both tables.

Code:

Output:

example-of-union-in-sql

Difference Between JOIN and UNION in SQL

In this section we will go through the differences between SQL JOIN and SQL UNION to learn more about SQL union vs join:

SQL JOIN	SQL UNION
The SQL JOIN is used when we have to extract data from more than one table.	The SQL UNION is used when we have to display the results of two or more SELECT statements.
In the case of SQL JOINS, the records are combined into new columns.	In the case of SQL UNION, the records are combined into new rows.
The SQL JOINS facilitates the joining of tables vertically.	The SQL UNION facilitates the connection of tables vertically.
The SQL JOINS are used to produce the given table's intersection.	The SQL UNION is used to produce the given table's conjunction.
The duplicate values can exist in SQL JOINS.	The duplicate values are removed by default in SQL UNION
To use SQL JOINS the two given tables need to have at least one column present within them.	To use SQL UNION the domain of the columns along with their attributes needs to be the same.

Conclusion

The SQL JOIN is used to combine two or more tables.
The SQL UNION is used to combine two or more SELECT statements.
The SQL JOIN can be used when two tables have a common column.
The SQL UNION can be used when the columns along with their attributes are the same.