Open In App

SQL | Union Clause

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

The Union Clause is used to combine two separate select statements and produce the result set as a union of both select statements.

 NOTE:

  1. The fields to be used in both the select statements must be in the same order, same number, and same data type.
  2. The Union clause produces distinct values in the result set, to fetch the duplicate values too UNION ALL must be used instead of just UNION.

Syntax for UNION:

SELECT column_name(s) FROM table1  

UNION 

SELECT column_name(s) FROM table2;

Syntax for UNION ALL:

The resultant set consists of distinct values.

SELECT column_name(s) FROM table1 

UNION ALL

SELECT column_name(s) FROM table2;

The resultant set consists of duplicate values too.

Consider we have two tables name Student and Student_Details. Suppose we want to do a union operation to find the common roll no from both tables. Let’s first create a table with the name student and insert some random data similarly, we will create another table with the name Student_details keeping in mind that there will be at least one column common here we will take roll_no as a common column.

CREATE :

CREATE TABLE students (
roll_no INT,
address VARCHAR(255),
name VARCHAR(255),
phone VARCHAR(20),
age INT
);
INSERT INTO students (roll_no, address, name, phone, age)
VALUES
(1, '123 Main St, Anytown USA', 'John Doe', '555-1234', 20),
(2, '456 Oak St, Anytown USA', 'Jane Smith', '555-5678', 22),
(3, '789 Maple St, Anytown USA', 'Bob Johnson', '555-9012', 19),
(4, '234 Elm St, Anytown USA', 'Sarah Lee', '555-3456', 21),
(5, '567 Pine St, Anytown USA', 'David Kim', '555-7890', 18);

Output:

IMG1

 

Let’s create a second table with the name Student details here it will contain three columns roll_no, branch, and grade. 

CREATE :

CREATE TABLE student_details (
roll_no INT,
branch VARCHAR(50),
grade VARCHAR(2)
);
INSERT INTO student_details (roll_no, branch, grade)
VALUES
(1, 'Computer Science', 'A'),
(2, 'Electrical Engineering', 'B'),
(3, 'Mechanical Engineering', 'C');

Output:

IMG2

 

UNION Clause

To fetch distinct ROLL_NO from Student and Student_Details table.

Query:

SELECT ROLL_NO FROM Students UNION 
SELECT ROLL_NO FROM Student_Details;

Output:

IMG3

 

The UNION ALL Clause

To fetch ROLL_NO from Student and Student_Details table including duplicate values.

Query:

SELECT ROLL_NO FROM Students UNION ALL 
SELECT ROLL_NO FROM Student_Details;

Output:

IMG4

 

The UNION ALL Clause with Where Condition

To fetch ROLL_NO, NAME from Student table WHERE ROLL_NO is greater than 3 and ROLL_NO, Branch from Student_Details table WHERE ROLL_NO is less than 3, including duplicate values and finally sorting the data by ROLL_NO.

Query:

SELECT ROLL_NO,NAME FROM Students WHERE ROLL_NO>3 
UNION ALL
SELECT ROLL_NO,Branch FROM Student_Details WHERE ROLL_NO<3
ORDER BY 1;

Output:

 

Note: The column names in both the select statements can be different but the data type must be same.And in the result set the name of column used in the first select statement will appear. 


Last Updated : 31 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads