SQL UNION ALL

The UNION ALL command in SQL is very similar to the UNION command. It allows you to concatenate the records of multiple queries, the only difference is that this command allows you to include all records, even duplicates. So, if the same record is normally present in the results of the two concatenated queries, then joining the two with UNION ALL will return the same result twice.
 

 

Syntax :

The syntax of the SQL query to join the results of the 2 tables is the following:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

 

 

Example :

Let’s say a company has databases for each of its customers. On these databases, there is a table of the list of products purchased by the customer with some information.

The table “customer1_products” corresponds to the first customer:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
|  105 | Gum      | fourniture |  250  | 1       |
+------+----------+------------+-------+---------+

The table “customer2_products” corresponds to the second store:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  106 | Monitor  | computer   |  45   | 100     |
|  107 | CD-ROM   | computer   |  100  | 40      |
|  108 | Modem    | computer   |  12   | 20      |
|  109 | Speaker  | computer   |  11   | 3       |
+------+----------+------------+-------+---------+

To concatenate all the records of these tables, it is possible to perform a single query using the UNION ALL command, like the example below:

SELECT * FROM customer1_products
UNION ALL
SELECT * FROM customer2_products

 

 
Output:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
|  105 | Gum      | fourniture |  250  | 1       |
|  106 | Monitor  | computer   |  45   | 100     |
|  107 | CD-ROM   | computer   |  100  | 40      |
|  108 | Modem    | computer   |  12   | 20      |
|  109 | Speaker  | computer   |  11   | 3       |
+------+----------+------------+-------+---------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More