MySQL - UNION vs UNION ALL

---

---

UNION and UNION ALL operators in MySQL are used to retrieve the rows from multiple tables and return them as one single table.

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.

MySQL UNION Operator

UNION operator in MySQL works similar to the union operator in relational algebra. This operator combines information from multiple tables that are union compatible. However, only distinct rows from the tables are added to the result table, as UNION automatically eliminates all the duplicate records.

Syntax

Following is the syntax of UNION operator in MySQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

MySQL UNION ALL Operator

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

Syntax

Following is the syntax of UNION ALL operator in MySQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

MySQL UNION VS MySQL UNION ALL

The only difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in both tables. Let's try to understand this with an example.

Example

First of all we need to create two tables namely with the same number of columns having the same data types. Following query creates a table named "COURSES_PICKED" −

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

Following query inserts values into the COURSES_PICKED table −

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

The contents of the COURSES_PICKED table will be as follows −

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

Now, lets create another table named EXTRA_COURSES_PICKED as shown below −

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 contents of the EXTRA_COURSES_PICKED table will be as follows −

STUDENT_ID	STUDENT_NAME	COURSES_PICKED
1	JOHN	PHYSICAL EDUCATION
2	ROBERT	GYM
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS

Combining tables using UNION Operator

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

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

Following is the output obtained −

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

In here, the extra courses and courses picked by "Julian" and "Shasha" are same therefore, the last two records in the COURSES_PICKED and EXTRA_COURSES_PICKED are identical. When we combine both the tables using the UNION operator it excludes the identical records and returns only distinct records.

Combining tables using UNION ALL Operator

Unlike the UNION operator if we combine the above created tables using the UNION ALL operator it reruns all the records in both tables −

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	COMMUNICATIONS
4	JULIAN	MATHEMATICS