SQL UNION ALL Operator

UNION ALL operator is used to get the combined results of two or more tables.
every select statement of tables must have the same fields name with the same data type and same order.
it is used to get all value from the two tables and value may be dulplcate.

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name1

UNION ALL

SELECT column_name(s) 
FROM table_name2

Example:- Suppose we have two table employees and users.

employees Table:-

+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

users table:-

   
+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email          | country |
+----+------------+-----------+----------------+---------+
|  1 | Ramesh     | Kumar     | ramesh@abc.com | India   |
|  2 | Suresh     | Kumar     | suresh@abc.com | India   |
|  3 | Anna       | Symonds   | anna@abc.com   | USA     |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)

Find all country names from the employees and users table.

Query:-

SELECT country FROM employees
UNION ALL
SELECT country FROM users

Output:-


+-----------+
| country   |
+-----------+
| USA       |
| USA       |
| Australia |
| Australia |
| India     |
| India     |
| India     |
| India     |
| India     |
| USA       |
+-----------+
10 rows in set (0.00 sec)