SELECT DISTINCT and UNION MYSQL

From the queries themselves also, you can get a rough idea. Anyway following is a simple explanation:

UNION:

SELECT City FROM Suppliers
UNION 
SELECT City FROM Customers;

The above query has combined two select queries into a single merged result. The result has values from the 'City' columns in both tables.

• SELECT City FROM Suppliers : This will retrieve all the values under the City column from the Supplier table.

• SELECT City FROM Customers : This will retrieve all the values under the City column from the Customer table.

• UNION : This will combine the values from both queries and output a single result. The speciality is it does not care about repetition. The result table can have duplicate values.

Refer to the following example for a clear understanding.

Suppliers Table | Supplier ID | Name | City | |-------------------|------------------|--------------| | 1 | David | New York | | 2 | John | Moscow | | 3 | Simon | Washington |

Customers Table | Customer ID | Name | City | |-------------------|------------------|--------------| | 1 | Ryan | Colombo | | 2 | Joe | Washington | | 3 | Kirt | Washington |

Query result | City | |-------------------| | New York | | Moscow | | Washington | | Colombo | | Washington | | Washington |

In the above result, you can see Washington is repeated.

DISTINCT:

SELECT DISTINCT S.City , C.City
FROM Suppliers S,Customers C;

In contrast to UNION, use of DISTINCT keyword result in filtering only the unique values from the retrieved values from the query.

If we consider the above query you have given, it says that to retrieve unique values under City column of both Supplier and Customer table. It will not repeat the same value again although they are in both tables.

So, considering the example tables I have provided above, the result will be as follows:

Query Result | City | |-------------------| | New York | | Moscow | | Washington | | Colombo |

You can see, now there is only one Washington is there. We can observe that only unique values are retrieved from the query.

Conlusion:

So, as I mentioned above the key difference between the two queries is query with UNIQUE retrieves all the data but the query with DISTINCT only retrieve unique values.