SQL Notes
Learn the difference between SQL JOIN and UNION, understand when to combine columns or rows, compare their behavior, syntax, performance, and real-world use cases.
One of the most common areas of confusion for SQL beginners is understanding the difference between JOIN and UNION.
Both are used to combine data, but they work in completely different ways.
Many developers initially assume that JOIN and UNION perform the same task because both combine information from multiple sources. However, their purpose, syntax, and results are very different.
Understanding the distinction between JOIN and UNION is important because it is a frequently asked interview topic and a fundamental database concept.
What is JOIN?
A JOIN combines columns from two or more related tables.
Example:
Customers Table
| CustomerID | CustomerName |
|---|---|
| 1 | Rahul |
| 2 | Priya |
Orders Table
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
Query:
SELECT
Customers.CustomerName,
Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Result:
| CustomerName | OrderID |
|---|---|
| Rahul | 101 |
| Priya | 102 |
JOIN combines:
Columns
from multiple tables.
What is UNION?
UNION combines rows from multiple SELECT statements.
Example:
Students Table
| Name |
|---|
| ------- |
| Rahul |
| Priya |
Teachers Table
| Name |
|---|
| ------- |
| Amit |
| Neha |
Query:
SELECT Name FROM Students
UNION
SELECT Name FROM Teachers;Result:
| Name |
|---|
| ------- |
| Rahul |
| Priya |
| Amit |
| Neha |
UNION combines:
Rows
from multiple queries.
Core Difference
JOIN:
Horizontal CombinationUNION:
Vertical CombinationVisual Representation:
JOIN:
Table A + Table B Name | OrderID ---------------- Rahul | 101 Priya | 102
UNION:
Table A
+
Table B
Name
------
Rahul
Priya
Amit
NehaJOIN Combines Columns
Example:
Employees
| EmployeeID | Name |
|---|---|
| 1 | Rahul |
Departments
| DepartmentID | DepartmentName |
|---|---|
| 1 | IT |
Query:
SELECT
Employees.Name,
Departments.DepartmentName
FROM Employees
JOIN Departments
ON Employees.EmployeeID =
Departments.DepartmentID;Result:
| Name | DepartmentName |
|---|---|
| Rahul | IT |
More columns are added.
UNION Combines Rows
Example:
CurrentEmployees
| Name |
|---|
| ------ |
| Rahul |
| Priya |
FormerEmployees
| Name |
|---|
| ------ |
| Amit |
| Neha |
Query:
SELECT Name
FROM CurrentEmployees
UNION
SELECT Name
FROM FormerEmployees;Result:
| Name |
|---|
| ------ |
| Rahul |
| Priya |
| Amit |
| Neha |
More rows are added.
JOIN Requirements
JOIN requires:
Related Tables
and
Join ConditionExample:
ON CustomerID =
CustomerIDWithout a relationship, JOIN is usually meaningless.
UNION Requirements
UNION requires:
Same Number of Columns
and
Compatible Data TypesExample:
Correct:
SELECT Name
FROM Students
UNION
SELECT Name
FROM Teachers;Both queries return:
One Text Column
JOIN Syntax
Example:
SELECT columns
FROM Table1
INNER JOIN Table2
ON Table1.ID =
Table2.ID;Common joins:
INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN SELF JOIN CROSS JOIN
UNION Syntax
Example:
SELECT column
FROM Table1
UNION
SELECT column
FROM Table2;Simple row combination.
JOIN Example in Detail
Customers:
| CustomerID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
Orders:
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
Query:
SELECT
Customers.Name,
Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Result:
| Name | OrderID |
|---|---|
| Rahul | 101 |
| Priya | 102 |
UNION Example in Detail
Table A:
| Name |
|---|
| ------ |
| Rahul |
| Priya |
Table B:
| Name |
|---|
| ------ |
| Priya |
| Amit |
Query:
SELECT Name
FROM TableA
UNION
SELECT Name
FROM TableB;Result:
| Name |
|---|
| ------ |
| Rahul |
| Priya |
| Amit |
Duplicate values are removed automatically.
UNION ALL
SQL also provides:
UNION ALLUnlike UNION:
Duplicates Are Kept
Example:
SELECT Name
FROM TableA
UNION ALL
SELECT Name
FROM TableB;Result:
| Name |
|---|
| ------ |
| Rahul |
| Priya |
| Priya |
| Amit |
JOIN vs UNION Comparison Table
| Feature | JOIN | UNION |
|---|---|---|
| Combines | Columns | Rows |
| Tables Required | Multiple | Multiple Queries |
| Relationship Needed | Yes | No |
| Join Condition Needed | Usually Yes | No |
| Adds New Columns | Yes | No |
| Adds New Rows | No | Yes |
| Duplicate Removal | No | UNION removes duplicates |
| Common Usage | Related Data | Similar Data |
Real-World Example: JOIN
E-Commerce System
Tables:
Customers Orders Products Payments
Requirement:
Customer Name
Order Number
Payment StatusSince data exists in different tables:
JOINis required.
Real-World Example: UNION
Company Database
Tables:
CurrentEmployees FormerEmployees
Requirement:
Complete Employee ListSolution:
UNIONbecause rows must be combined.
When to Use JOIN
Use JOIN when:
Data Exists In Different Tables Relationships Exist Additional Columns Are Needed
Examples:
- Customers and Orders
- Employees and Departments
- Students and Courses
When to Use UNION
Use UNION when:
Similar Data Exists In Multiple Tables
Additional Rows Are NeededExamples:
- Current and Archived Orders
- Current and Former Employees
- Domestic and International Customers
Performance Comparison
JOIN:
Performance Depends On:
Indexes
Join Conditions
Table SizeUsually efficient when properly indexed.
UNION:
UNION
ā
Removes Duplicates
ā
Additional ProcessingCan be slower.
UNION ALL:
No Duplicate RemovalUsually faster than UNION.
Common Errors
Using JOIN Instead of UNION
Wrong:
Trying To Append Rows
Using JOINJOIN combines columns, not rows.
Using UNION Instead of JOIN
Wrong:
Trying To Retrieve Related Data
Using UNIONUNION combines rows, not relationships.
Different Column Counts
Wrong:
SELECT Name
UNION
SELECT Name, Age;Produces an error.
Incompatible Data Types
Example:
Text Column
UNION
Date Columnmay fail.
Best Practices
Use JOIN for Relationships
When tables are connected through keys.
Use UNION for Similar Data
When datasets have the same structure.
Use UNION ALL When Duplicates Are Allowed
Improves performance.
Keep Queries Readable
Use aliases and proper formatting.
Understand Business Requirements First
Determine whether you need:
More Columns or More Rows
before choosing JOIN or UNION.
Common Interview Questions
What is the main difference between JOIN and UNION?
JOIN combines columns.
UNION combines rows.
Does UNION remove duplicates?
Yes.
UNION removes duplicates automatically.
Which is faster: UNION or UNION ALL?
UNION ALL.
Because no duplicate removal occurs.
Does JOIN require a relationship?
Usually yes.
Tables are joined using related columns.
When should JOIN be used?
When retrieving related data from multiple tables.
Summary
JOIN and UNION are both used to combine data, but they serve completely different purposes. JOIN combines related columns from multiple tables, while UNION combines rows from multiple queries.
In this lesson, you learned:
- What JOIN is
- What UNION is
- Horizontal vs Vertical combination
- JOIN syntax
- UNION syntax
- UNION ALL
- Performance differences
- Real-world examples
- Common mistakes
- Interview questions
Understanding JOIN vs UNION is essential because choosing the wrong operation can produce incorrect results and inefficient queries.
Next Step
Continue to the next lesson:
Subquery Introduction ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for JOIN vs UNION.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, joins, join, union
Related SQL Complete Guide Topics