Categories
28°C, New York
Home SQL SQL UNION ALL

SQL UNION ALL

SQL UNION Operator

SQL | Except Clause

SQL UNION ALL command combines the result of two or more SELECT statements in SQL.

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.

Syntax

The syntax for the SQL UNION ALL operation is:

SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;

SQL Union All vs UNION

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

SQL UNION ALL Examples

Let’s look at some examples of the UNION ALL command in SQL to understand its working.

First, let’s create a demo SQL database and table on which UNION ALL will be performed.

Demo SQL Database

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

STUDENTS table:

ROLL_NONAMEDOBAGE
1DEV SHARMA2001-08-1617
2AMAN VERMA2002-01-0416
3KRISH VATSA2000-11-2918

TRIP_DETAIL Table:

ROLL_NONAMEDOBAGE
1DEV SHARMA2001-08-1617
2AMAN VERMA2002-01-0416
3KRISH VATSA2000-11-2918
4VARUN GOYAL2003-09-2115

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

SQL 
 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

Important {Points About SQL UNION All

  • 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.

Union All Operator – FAQs

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.


Tags:
#SQL-Clauses-Operators #SQL

SQL UNION Operator

SQL | Except Clause