SQL advanced -> UNION and UNION ALL

When UNION clause is used, SQL engine by default excludes repeating records from result dataset. The way to present them is to add "ALL" option:
/* to select ALL names from Employees and Customers we'll use "UNION ALL", to select distinct names we'll use "UNION" */

/* in MS-Access */
SELECT name, count(*) AS name_count
FROM
(
  SELECT LCase(Trim(FirstName))& ' ' & LCase(Trim(LastName)) AS Name
  FROM Employees
   UNION ALL
  SELECT LCase(Trim(CustomerName)) AS Name
  FROM Customers
) AS a
GROUP BY name;

/* in TSQL */
SELECT name, count(*) AS name_count
FROM
(
  SELECT Lower(RTrim(FirstName))+ ' ' + Lower(RTrim(LastName)) AS name
  FROM Employees
   UNION ALL
  SELECT Lower(RTrim(CustomerName)) AS name
  FROM Customers
) AS a
GROUP BY name;


sqlexamples.info