There is a function in MySQL Query is UNION. Its function is to combine data records from two different tables.
Note: the number of fields in the select in both tables must be equal.
In our discussion this time about the difference between UNION with UNION ALL.
As a result, UNION if the two tables there are the same data, it will display one of them, the first data.
UNION ALL will display all existing data on both tables, although there is the same data.
For more details, look at this example:
UNION
Example I
Table_1
| ID | NAME | SCORE |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | HOWARD | 320 |
MySQL Query : "SELECT ID, NAME, SCORE FROM Table_1 UNION SELECT ID, NAME, SCORE FROM Table_2";
Result :
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
| 2 | HOWARD | 320 |
Example II
Table_1
Table_1
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | JACK | 330 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | JACK | 310 |
MySQL Query : "SELECT ID, NAME, SCORE FROM Table_1 UNION SELECT ID, NAME, SCORE FROM Table_2";
Result :
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | JACK | 310 |
| 2 | JACK | 330 |
Example III
Table_1
| ID | NAME | SCORE |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | HOWARD | 320 |
MySQL Query : "SELECT NAME FROM Table_1 UNION SELECT NAME FROM Table_2";
Result :
| NAME |
| DIANA |
| HOWARD |
| JACK |
UNION ALL
Example I
Table_1
| ID | NAME | SCORE |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | HOWARD | 320 |
MySQL Query : "SELECT ID, NAME, SCORE FROM Table_1 UNION ALL SELECT ID, NAME, SCORE FROM Table_2";
Result :
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
| 2 | HOWARD | 320 |
Example II
Table_1
Table_1
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | JACK | 330 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | JACK | 310 |
MySQL Query : "SELECT ID, NAME, SCORE FROM Table_1 UNION ALL SELECT ID, NAME, SCORE FROM Table_2";
Result :
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 1 | DIANA | 350 |
| 2 | JACK | 310 |
| 2 | JACK | 330 |
Example III
Table_1
| ID | NAME | SCORE |
| 1 | JACK | 300 |
| 2 | DIANA | 310 |
Table_2
| ID | NAME | SCORE |
| 1 | DIANA | 350 |
| 2 | HOWARD | 320 |
MySQL Query : "SELECT NAME FROM Table_1 UNION ALL SELECT NAME FROM Table_2";
Result :
| NAME |
| DIANA |
| DIANA |
| HOWARD |
| JACK |
