How to fix incorrect row count in MySQL?

Question

MySQL

There are several tables with a total of 12 rows

. To get the number, I created a procedure:

BEGIN
SELECT sum(cnt) as 'Всего записей' from ( 
        select count(*) as cnt from xx
  UNION select count(*) as cnt from visa 
  UNION select count(*) as cnt from xvii
  UNION select count(*) as cnt from xviii 
  UNION select count(*) as cnt from xix 
  UNION select count(*) as cnt from states
  UNION select count(*) as cnt from xxi) as t;
END

Without the info table, there should be 11 rows in total, but the procedure only outputs the value 7. All tables have the same fields

Answer 1

UNION is executed as DISTINCT by default, that is, only unique rows will be in the resulting table, instead of three twos, only one will be included. Replace UNION with UNION ALL.