SQL - UNION vs UNION ALL

---

---

SQL UNION and UNION ALL operators combine rows from multiple tables into a single result set. UNION returns only distinct rows, while UNION ALL returns all rows, including duplicates.

For these operators to work, the tables must have the same number of columns with matching data types, though the number of rows can differ. The column names from the first table determine the column names in the result, and matching columns from other tables are merged accordingly.

Difference Between SQL UNION and UNION ALL

The following table shows the basic differences between UNION and UNION ALL operators in SQL:

SQL UNION Operator

The UNION operator in SQL combines data from multiple tables that are union-compatible, similar to the union operation in relational algebra.

It returns only distinct rows in the result set, automatically eliminating any duplicate records from the combined tables.

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;

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

SQL UNION ALL Operator

UNION ALL is an SQL operator used to combine data from multiple tables into a single result set, including all duplicate rows.

For example, consider two tables: one listing the number of international games a player has played, and the other listing national games.

In the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the numbers are the same, these represent separate matches. To include both rows in the combined result showing the total matches played, we use the UNION ALL operator.

Syntax

Following is the syntax of UNION ALL operator in SQL:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

Let us perform a UNION ALL operation on the sample tables COURSES_PICKED and EXTRA_COURSES_PICKED. The query for this operation is shown below:

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

The resultant table is displayed as follows: