Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Jul 9, 2024
Medium

Difference Between JOIN and UNION in SQL

Author Jay Dhoot
0 upvote
Table of contents
Learn to use AI Tools & ChatGPT to excel as Microsoft SDE
11 Jul, 2024 @ 01:30 PM
Speaker
Pranav Malik
SDE 2 @

Introduction

UNION and JOIN are the two most frequent clauses used in SQL queries, but we often use them inappropriately. In this article, we will see some of the significant differences between UNION and JOIN in SQL.

Difference Between Union and Join

SQL Join statement helps in retrieving data from many different tables based on a common condition between them, while Union is a set operation that helps in combining the result of two SELECT statements.

What is Join in SQL?

In a relational database, foreign keys help maintain the relationship between the tables. Join in SQL helps to retrieve data from related tables based on the common field.

There are four types of Join present in SQL:

  1. Right Join: This is a type of join that results in all the data from the right table and the matched data from the left table based on the matching conditions.
     
  2. Left Join: This is a type of Join that results in all the data from the left table and the matched data from the right table based on the matching conditions.
     
  3. Inner Join: This join returns only the matching values from both tables.
     
  4. Outer or Full Join: This join returns all the data from the left table and the right table. For the rows which do not match, the resultant rows would have NULL values.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is Union in SQL?

In SQL, a UNION is a clause used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows by default and sorts the result set based on the columns selected in the first SELECT statement, unless explicitly specified with the ORDER BY clause. The UNION operator is commonly used to merge data from multiple tables or queries with similar structures.

Difference between UNION and JOIN in SQL

Below table shows the difference between UNION and JOIN in SQL on the basis of definition, data type, type of combination, number of columns selected, uniqueness, and direction.

BasisUnionJoin
DefinitionReturns the data as a combination of two or more SELECT statements.Returns the data from multiple tables based on the matching condition
Data TypeThe data types of the respective columns from each table should be the same.The data types of the two tables can be different.
Type of CombinationColumn-Wise Combination. Combines data into new columns. Row-Wise Combination combines data into new rows.
Number of Columns SelectedIt should be the same in all the tables.It may or may not be the same in all the tables.
UniquenessReturns distinct rows.It can return distinct as well as duplicate rows.
DirectionVertical CombinationHorizontal Combination

Examples of UNION and JOIN 

We will cover some examples of Union and join

Example of Union

Let's create two tables named, Ninja_1 and Ninja_2.

Table1: Ninja_1

-- create a table
CREATE TABLE Ninja_1 (
  student_id INT PRIMARY KEY,
  student_name varchar(30) NOT NULL,
  student_city varchar(30) NOT NULL
);


-- insert some values
INSERT INTO Ninja_1 VALUES (1, 'Ninja1', 'Jaipur');
INSERT INTO Ninja_1 VALUES (3, 'Ninja3', 'Mumbai');
INSERT INTO Ninja_1 VALUES (4, 'Ninja4', 'Mumbai');
INSERT INTO Ninja_1 VALUES (7, 'Ninja7', 'Ahmedabad');


-- fetch the table
SELECT * FROM Ninja_1;
Table1: Ninja_1


Table 2: Ninja_2

-- create a table
CREATE TABLE Ninja_2 (
  student_id INT PRIMARY KEY,
  student_name varchar(30) NOT NULL,
  student_marks INT NOT NULL
);


-- insert some values
INSERT INTO Ninja_2 VALUES (8, 'Ninja8', 89);
INSERT INTO Ninja_2 VALUES (10, 'Ninja10', 65);
INSERT INTO Ninja_2 VALUES (2, 'Ninja2', 87);
INSERT INTO Ninja_2 VALUES (5, 'Ninja5', 90);
INSERT INTO Ninja_2 VALUES (6, 'Ninja6', 76);


-- fetch some values
SELECT * FROM Ninja_2;
Table 2: Ninja_2

Suppose we have to display all the student names with their ids. In this case, we must perform the UNION operation between the two tables.

SQL Query

SELECT student_id as id,student_name as name  
FROM Ninja_1    
UNION    
SELECT student_id as id,student_name as name
FROM Ninja_2; 


Output

Output of Union Clause

Example of Join

Let's create two tables named, Ninjas and Coding.

Table 1: Ninjas 

-- create a table
CREATE TABLE Ninjas (
  student_id INT PRIMARY KEY,
  student_name varchar(30) NOT NULL,
  student_city varchar(30) NOT NULL
);


-- insert some values
INSERT INTO Ninjas VALUES (1, 'Ninja1', 'Jaipur');
INSERT INTO Ninjas VALUES (3, 'Ninja3', 'Mumbai');
INSERT INTO Ninjas VALUES (4, 'Ninja4', 'Mumbai');
INSERT INTO Ninjas VALUES (7, 'Ninja7', 'Ahmedabad');


-- fetch the table
SELECT * FROM Ninjas;
Table 2: Ninjas
Table 1: Ninjas

Table 2: Coding 

-- create a table
CREATE TABLE Coding (
  course_id INT PRIMARY KEY,
  course_name varchar(30) NOT NULL,
  student_id FOREIGN KEY REFERENCES Ninjas(student_id),
  performance INT NOT NULL
  
);


-- insert some values
INSERT INTO Coding VALUES (56, 'dsa_advanced', 4, 75);
INSERT INTO Coding VALUES (32, 'dbms', 1, 45);
INSERT INTO Coding VALUES (12, 'os', 3, 80);
INSERT INTO Coding VALUES (90, 'web_dev', 7, 90);


-- fetch some values
SELECT * FROM Coding;
Table 2: Coding

Suppose we have to display student_id, student_name, course_name, and performance. As these columns are not in the same table, thus we will have to join them to produce the required outcome.

SQL Query

SELECT N.student_ID, N.student_name, C.course_name, C.performance      
FROM Ninjas as N    
JOIN Coding as C      
ON N.student_id = C.student_id;  


Output

Output of Join Clause

Frequently Asked Questions

What is the difference between UNION and JOIN in Access?

In Access, a UNION combines the results of two or more SELECT queries into a single result set, while a JOIN links tables based on a common field to retrieve related data from multiple tables in a single query.

Which is faster UNION or JOIN?

In general, JOIN operations tend to be faster than UNION operations because JOINs retrieve related data from tables based on specified criteria, whereas UNIONs involve merging and sorting multiple result sets, which can be more resource-intensive.

What is the difference between joins and set operators?

Joins in SQL link tables based on common fields to retrieve related data, while set operators like UNION, INTERSECT, and EXCEPT combine the results of queries without necessarily relating data between tables.

What is the difference between UNION and JOIN in Tableau?

In Tableau, a JOIN is used to combine data from different tables based on a common field, whereas a UNION is used to stack data vertically from tables with similar structures, essentially appending rows from one table to another.

What is the difference between UNION and UNION ALL?

UNION returns unique records, while UNION ALL may or may not have unique records. UNION ALL returns all the data, including the duplicate ones.

Conclusion

In this article, we have discussed the difference between union and join in SQL. We have also seen how we can perform the union and join operations on different tables. 
I hope this article has helped you understand the differences between UNION and JOIN in SQL. To learn more about joins in SQL, you can refer to - Joins, Types of Joins.

Happy Coding !!