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:
SQL | Union Clause
The Union Clause is used to combine two separate select statements and produce the result set as a union of both select statements.
NOTE:
- The fields to be used in both the select statements must be in the same order, same number, and same data type.
- 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:
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: