SQL UNION Operator

The UNION operator is used to combine the results of two or more SELECT queries without returning any duplicate rows.

Each SELECT statement within the UNION must have:

• The same number of columns.
• The corresponding columns must have the same data types.
• The columns must be in the same order.

UNION Syntax

The syntax of the UNION operator is as follows:

SELECT column_list FROM table1
UNION 
SELECT column_list FROM table2;

UNION ALL Syntax

By default, the UNION operator will select only distinct values. If you want to fetch duplicate values, use the UNION ALL operator.

The syntax of the UNION ALL operator is as follows:

SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;

Demo Table

To understand the UNION operator, let us suppose that we have the following "Employee" and "Person" tables:

• Table: Employee :

  +--------+-------------+-----------+-----------+
  | emp_no | first_name  | last_name | salary    |
  +--------+-------------+-----------+-----------+
  | 1001   | James       | Smith     | 6600      |
  | 1002   | Maria       | Martinez  | 9000      |
  | 1003   | Andrew      | Rodriguez | 9500      |
  | 1004   | Robert      | Hernandez | 9600      |
  | 1005   | Donna       | Williams  | 7400      |
  | 1006   | James       | Johnson   | 8000      | 
  | 1007   | Stephanie   | Miller    | 8500      |
  +--------+-------------+-----------+-----------+
  

• Table: Person :

  +--------+-------------+-----------+-----------+
  | emp_no | first_name  | last_name | city      |
  +--------+-------------+-----------+-----------+
  | 2001   | Maria       | Garcia    | Austin    |
  | 2002   | James       | Smith     | Columbus  |
  | 2003   | Charles     | Lopez     | Seatle    |
  | 2004   | Christoper  | Miller    | Boston    |
  | 2005   | Jennifer    | Taylor    | Denver    |
  | 2006   | Joseph      | Lee       | Boston    |
  | 2007   | Patricia    | Smith     | Baltimore |    
  +--------+-------------+-----------+-----------+
  

SQL UNION Example

Let us perform a union operation to combine the results of two queries.

The following SQL statement will return the first and last names (only distinct values) from both the "Employee" and "Person" table:

SELECT first_name, last_name FROM employee
UNION 
SELECT first_name, last_name FROM person;

After executing the above statement, the result set will be as follows:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| James       | Smith     |
| Maria       | Martinez  |
| Andrew      | Rodriguez |
| Robert      | Hernandez |
| Donna       | Williams  |
| James       | Johnson   |
| Stephanie   | Miller    |
| Maria       | Garcia    |
| Charles     | Lopez     |
| Christoper  | Miller    |
| Jennifer    | Taylor    |
| Joseph      | Lee       |
| Patricia    | Smith     | 
+-------------+-----------+

The UNION operator eliminates the duplicate records from the combined result set. That is why the above query returns 13, because if you can see the name "James Smith" appears in both the "Employee" and "Person" tables.

SQL UNION ALL Example

The following SQL statement will return the first and last names (duplicate values also) from both the "Employee" and "Person" table:

SELECT first_name, last_name FROM employee
UNION ALL
SELECT first_name, last_name FROM person;

After executing the above statement, the result set will be as follows:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| James       | Smith     |
| Maria       | Martinez  |
| Andrew      | Rodriguez |
| Robert      | Hernandez |
| Donna       | Williams  |
| James       | Johnson   |
| Stephanie   | Miller    |
| Maria       | Garcia    |
| James       | Smith     |
| Charles     | Lopez     |
| Christoper  | Miller    |
| Jennifer    | Taylor    |
| Joseph      | Lee       |
| Patricia    | Smith     | 
+-------------+-----------+

The UNION ALL operator will not eliminate the duplicate record from the combined result set. As we can see above, the name "James Smith" appears twice in the result set.

SQL UNION With WHERE

The following SQL statement will return the first and last names (only distinct values) from both the "Employee" and "Person" table where the last name is "Smith":

SELECT first_name, last_name FROM employee
UNION
SELECT first_name, last_name FROM person
WHERE last_name = 'Smith'

The output of the above statement will be as follows:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| James       | Smith     |
| Patricia    | Smith     |
+-------------+-----------+

SQL UNION With WHERE

The following SQL statement will return the first and last names (duplicate values also) from both the "Employee" and "Person" table where the last name is "Smith":

SELECT first_name, last_name FROM employee
UNION
SELECT first_name, last_name FROM person
WHERE last_name = 'Smith'

After executing the above statement, the result set will be as follows:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| James       | Smith     |
| James       | Smith     |
| Patricia    | Smith     |
+-------------+-----------+