WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL UNION Operator



The SQL UNION operation matches the results of two or more SELECT statements.


SQL UNION operator

The UNION operator is used to combine the result sets of two or more SELECT statements.

Please note that each SELECT statement inside a UNION must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.

SQL UNION syntax

SELECT column_name (s) FROM table1
UNION
SELECT column_name (s) FROM table2 ;

Note: By default, the UNION operator picks different values. If duplicate values ​​are allowed, use UNION ALL.

SQL UNION ALL syntax

SELECT column_name (s) FROM table1
UNION ALL
SELECT column_name (s) FROM table2 ;

Note: The column names in the UNION result set are always equal to the column names in the first SELECT statement in UNION.


Demo database

In this tutorial, we will use the welookups sample database.

Here is the data from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/  | 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/ | 13    | IN      |
| 3  | zomato      | http://www.zomato.com/  | 4689  | IN      |
| 4  | indiamart          | http://indiamart.com/       | 20    | IN      |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data for the "apps" APP:

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | zomato     | http://zomato.com/     | IN      |
|  2 | facebook APP | http://facebook.com/     | IN      |
|  3 | indiamart APP | https://www.indiamart.com/| IN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)


SQL UNION instance

The following SQL statement selects all different country (only different values) from the "Websites" and "apps" tables:

Example

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

The result of executing the above SQL is as follows:

Note: UNION cannot be used to list all countries in two tables. If some websites and apps come from the same country, each country will be listed only once. UNION will only pick different values. Use UNION ALL to pick duplicate values!


SQL UNION ALL instance

The following SQL statement uses UNION ALL to select all country (also have duplicate values) from the "Websites" and "apps" tables:

Example

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

The result of executing the above SQL is as follows:



SQL UNION ALL with WHERE

The following SQL statement uses UNION ALL to select all INDIA (IN) data from the "Websites" and "apps" tables (there are also duplicate values):

Example

SELECT country, name FROM Websites
WHERE country='IN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='IN'
ORDER BY country;