Difference Between JOIN and UNION in SQL

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.

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;

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;

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

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 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;

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

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 !!