SQL - UNION vs UNION ALL

UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables.

However, for these operators to work on these tables, they need to follow the conditions given below −

The tables to be combined must have the same number of columns with the same datatype.

The number of rows need not be the same.

Once these criteria are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table.

Note − Column names of first table will become column names of the resultant table, and contents of second table will be merged into resultant columns of same data type.

What is UNION?

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.

Only distinct rows from the tables are added to the result table, as UNION automatically epminates all the duppcate records.

Syntax

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having the same data types.

Create table COURSES_PICKED using the following query −

SQL> CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

SQL> INSERT INTO COURSES_PICKED VALUES(1,  JOHN ,  ENGLISH );
INSERT INTO COURSES_PICKED VALUES(2,  ROBERT ,  COMPUTER SCIENCE );
INSERT INTO COURSES_PICKED VALUES(3,  SASHA ,  COMMUNICATIONS );
INSERT INTO COURSES_PICKED VALUES(4,  JULIAN ,  MATHEMATICS );

The table will be displayed as −

+------------+--------------+------------------+
| STUDENT_ID | STUDENT_NAME | COURSE_NAME      |
+------------+--------------+------------------+
|          1 | JOHN         | ENGLISH          |
|          2 | ROBERT       | COMPUTER SCIENCE |
|          3 | SASHA        | COMMUNICATIONS   |
|          4 | JULIAN       | MATHEMATICS      |
+------------+--------------+------------------+

Create table EXTRA_COURSES_PICKED using the following query −

SQL> CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

SQL> INSERT INTO EXTRA_COURSES_PICKED VALUES(1,  JOHN ,  PHYSICAL EDUCATION );
INSERT INTO EXTRA_COURSES_PICKED VALUES(2,  ROBERT ,  GYM );
INSERT INTO EXTRA_COURSES_PICKED VALUES(3,  SASHA ,  FILM );
INSERT INTO EXTRA_COURSES_PICKED VALUES(4,  JULIAN ,  MATHEMATICS );

The table will be created as shown below −

+------------+--------------+--------------------+
| STUDENT_ID | STUDENT_NAME | COURSES_PICKED     |
+------------+--------------+--------------------+
|          1 | JOHN         | PHYSICAL EDUCATION |
|          2 | ROBERT       | GYM                |
|          3 | SASHA        | FILM               |
|          4 | JULIAN       | MATHEMATICS        |
+------------+--------------+--------------------+

Now, let us try to combine both these tables using the UNION query as follows −

SQL> SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is −

+------------+--------------+--------------------+
| STUDENT_ID | STUDENT_NAME | COURSE_NAME        |
+------------+--------------+--------------------+
|          1 | JOHN         | ENGLISH            |
|          1 | JOHN         | PHYSICAL EDUCATION |
|          2 | ROBERT       | COMPUTER SCIENCE   |
|          2 | ROBERT       | GYM                |
|          3 | SASHA        | COMMUNICATIONS     |
|          3 | SASHA        | FILM               |
|          4 | JULIAN       | MATHEMATICS        |
+------------+--------------+--------------------+

What is UNION ALL?

UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one. However, this operator also preserves the duppcate rows in the resultant tables.

Suppose there are two tables, one of which contains the number of games a player competed in, internationally and the other contains the number of games a player played nationally.

As we can see in the tables above, Kohp played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the result table displaying the total matches played by a player. So, we use UNION ALL operator in such cases.

Syntax

Following is the syntax of UNION ALL operator in SQL −

SQL> SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

In the following example, let us try to perform UNION ALL operation on the same sample tables given above: “COURSES_PICKED” and “EXTRA_COURSES_PICKED”, using the given query below −

SQL> SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table is displayed as follows −

+------------+--------------+--------------------+
| STUDENT_ID | STUDENT_NAME | COURSE_NAME        |
+------------+--------------+--------------------+
|          1 | JOHN         | ENGLISH            |
|          2 | ROBERT       | COMPUTER SCIENCE   |
|          3 | SASHA        | COMMUNICATIONS     |
|          4 | JULIAN       | MATHEMATICS        |
|          1 | JOHN         | PHYSICAL EDUCATION |
|          2 | ROBERT       | GYM                |
|          3 | SASHA        | FILM               |
|          4 | JULIAN       | MATHEMATICS        |
+------------+--------------+--------------------+