Another question on Mysql?

Question

MySQL

I want to get the number of all records from 3 tables with identical structure where the column is private='0'
I wanted to do it like this:

SELECT COUNT(*) FROM users_data, deleted, mails WHERE private='0'

But this doesn't work:

#1052 - 'private' column in where clause is ambiguous

After searching for information by mistake, I decide to do this:

SELECT COUNT(*) FROM users_data, deleted, mails WHERE users_data.private='0' AND deleted.private='0' AND mails.private='0'

But in the end, the command takes a long time to load and is not executed, the gateway timeout error is displayed.
I don’t think it’s about the amount of data, there are about 300k lines in total, since individually everything works fine: The request is executed quickly
SELECT COUNT(*) FROM users_data WHERE private='0'

Answer 1

You have multiplied the tables, getting a very large number of elements. You need to combine the output via UNION.

SELECT SUM(cnt) FROM (
  SELECT COUNT(*) as cnt FROM users_data WHERE private='0'
  UNION
  SELECT COUNT(*) as cnt FROM deleted WHERE private='0'
  UNION
  SELECT COUNT(*) as cnt FROM mails WHERE private='0'
) u;