SQL - UNION and UNION ALL

I think that all of us used until now UNION in a SQLstatement. Using this operator we can combine the result of 2 queries.

For example we want to display the name of all our clients.In our database the clients are stored in separate databases based on theregion. To be able to display the name of all our clients we need to execute aquery on different table and combine the result:

SELECT Name FROM EuropeClients
UNION
SELECT Name FROM AsiaClients

Each time when we execute this call everything is fine. Inone day, the marketing team observes that there are clients that appear inEurope, but also in Asia, but when we execute this query we don’t have anyduplicated data.

This happen because UNION removes any duplicates rows.Because of this, if we want to count how many time a client name appears, itwill not be possible using just UNION.

SELECT Name, count(*) AS Count
FROM
(SELECT Name FROM EuropeClients
UNION
SELECT Name FROM AsiaClients)
GROUP BY Name

For this query, the Count column will be 1 everywhere. For thesecases we need to use an optional argument that UNION operator have. The ALLargument displays the duplicates rows also. Using this argument with UNION willpermit to get the result that we expected from our queue.

SELECT Name, count(*) AS Count
FROM
(SELECT Name FROM EuropeClients
UNION ALL
SELECT Name FROM AsiaClients)
GROUP BY Name

Maybe the example was not the best one. But I would like to emphasize that theUNION don’t return the duplicates rows, but UNION ALL do.
Also, before using anykind of SQL commands try to understand 100% what they do.