UNION and CONCAT

Date: April 26th 2016
Last updated: April 26th 2016

UNION can join the results from multiple select statements, while CONCAT joins similar datatypes together. The following example was adapted from a challenge set on https://www.hackerrank.com/.

Example

SELECT 
CONCAT(name, '(', SUBSTR(country, 1, 1), ')')
FROM (SELECT 
      name, country 
      FROM my_world_table
      ORDER BY name) a
UNION
SELECT
CONCAT('There are ',b.co,' people present from ',b.country)
FROM (SELECT
     country, count(country) AS co
     FROM occupations
     GROUP BY country
     ORDER BY co, country) b
;

/*
Abby(A)
Harry(A)
Ray(N)
Richard(N)
Miles(N)
There are 2 people present from Australia
There are 3 people present from New Zealand