SQL UNION ALL Operator

SQL UNION ALL operator combines the result of two or more SELECT statements in SQL. It allows duplicate values in the result set.

For performing the UNION ALL operation, it is necessary that both the SELECT statements should have an equal number of columns/fields otherwise the resulting expression will result in an error.

SQL Union All vs UNION

SQL UNION ALL operator is different from UNION as the UNION operator removes duplicates and UNION ALL does not.

Syntax

The syntax for the SQL UNION ALL operation is:

SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;

Demo SQL Database

In this tutorial on the UNION ALL operator, we will use the following table in examples.

STUDENTS table:

ROLL_NO	NAME	DOB	AGE
1	DEV SHARMA	2001-08-16	17
2	AMAN VERMA	2002-01-04	16
3	KRISH VATSA	2000-11-29	18

TRIP_DETAIL Table:

ROLL_NO	NAME	DOB	AGE
1	DEV SHARMA	2001-08-16	17
2	AMAN VERMA	2002-01-04	16
3	KRISH VATSA	2000-11-29	18
4	VARUN GOYAL	2003-09-21	15

To create these tables on your system, write the following SQL query.

MySQL

 CREATE TABLE STUDENTS(
     ROLL_NO INT,
     NAME VARCHAR(20),
     DOB DATE,
     AGE INT(2));
     
CREATE TABLE TRIP_DETAIL(
     ROLL_NO INT,
     NAME VARCHAR(20),
     DOB DATE,
     AGE INT(2));     
 
 INSERT INTO STUDENTS VALUES
    (1,"DEV SHARMA","2001-08-16",17),
    (2,"AMAN VERMA","2002-01-04",16),
    (3,"KRISH VATSA","2000-11-29",18);

INSERT INTO TRIP_DETAIL VALUES
    (1,"DEV SHARMA","2001-08-16",17),
    (2,"AMAN VERMA","2002-01-04",16),
    (3,"KRISH VATSA","2000-11-29",18),
    (4,"VARUN GOYAL","2003-09-21",15);

SQL UNION ALL Example

Lets look at an example of UNION ALL operator in SQL. This example demonsrate how to use the SQL UNION ALL operator in SQL query and help to practice UNION ALL.

   SELECT * FROM STUDENTS
   UNION ALL
   SELECT * FROM TRIP_DETAIL;

Output

Performing UNION ALL

Key Takeaways About All Operator

• UNION ALL command helps us to combine results of two or more SELECT statements from different tables.
• The UNION ALL command includes the duplicate records from the SELECT statements whereas the UNION command does not include duplicate records otherwise both the commands are same.
• For performing the UNION ALL operation, it is necessary that both the SELECT statements should have equal number of columns otherwise the resulting expression will result in an error.

FAQs on Union All Operator

What is UNION ALL command?

UNION ALL command obtains combined records of two or more SELECT statement from two or more than two tables.

Is duplicates records allowed in UNION ALL?

Yes, duplicates records are allowed in the UNION ALL command but not in UNION command.

Is it necessary to have same number of columns in all SELECT queries of UNION ALL?

Yes, it necessary to have same number of columns in all SELECT queries otherwise an error will occur.