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)