MySQL QUERY : UNION VS UNION ALL


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
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
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


EmoticonEmoticon