MySQL, merging 2 or more tables before execute SELECT DISTINCT query?

Question

I want to calculate how many unique logins from 2 (or probably more tables).

I tried this:

SELECT count(distinct(l1.user_id)) 
FROM `log_1` l1 
LEFT JOIN `log_2` l2 
ON l1.userid = l2.userid;

But it gives me result of l1. If I didnt put l1 on li.userid (distinct), it said "ambiguous".

How do I combine the table, and then select unique login of the combined table?

EDIT:

Tested: I test the count(distinct(l1.userid)) and count(distinct(l2.userid)). It gives me different result

Answer 1

If you are using LEFT JOIN then you will get at least one row in the combined result for each row in l1, so the join is entirely unnecessary if you just want a distinct count. This would give you the same result as your query:

SELECT count(distinct(l1.user_id)) 
FROM `log_1` l1 

Perhaps you want an INNER JOIN or UNION instead? A UNION will count a user if they appear in either table. An INNER JOIN will count them only if they appear in both tables. Here's an example of the UNION:

SELECT count(*) FROM (
    SELECT distinct(user_id) FROM `log_1`
    UNION
    SELECT distinct(user_id) FROM `log_2`
) T1