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 criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table.

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 just combines the information from multiple tables that are union compatible.

Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records.

Syntax

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two tables 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 −

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 −

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

The table will be displayed as shown below −

Now, let us create another table EXTRA_COURSES_PICKED using the following query −

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 −

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

The table will be created as shown below −

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

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is as follows −

What is UNION ALL?

UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one table. However, this operator also preserves the duplicate 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, Kohli 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 resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases.

Syntax

Following is the syntax of UNION ALL operator in SQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

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

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table is displayed as follows −