What's the Difference between JOIN and UNION in SQL?
FREE
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:
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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.
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:
Turn Learning into Career Growth
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.
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:
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.
Learn more
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more