Alternative way of full outer join

Question

I am running this query

select * from
(select name, count(distinct id) as ids, date 
from table1
group by name, date ) as tt
full outer join
(select st_name as name,count(distinct id) as ids, date 
from table2  
group by st_name, date) as ts
on tt.name= ts.name
and tt.ids = ts.ids

It runs successfully but I want to ask if there is an alternative more efficient way to run this query.

Answer 1

I assume that you want to get days when the two numbers are not the same (it seems like the most reasonable thing you want from such a query). So, this addresses that question.

FULL OUTER JOIN should be fine. But an alternative is to try UNION ALL and aggregation:

select name, sum(ids_1), sum(ids_2), date
from ((select name, count(distinct id) as ids_1, NULL as ids_2, date 
       from table1
       group by name, date
      ) 
      union all
      (select st_name as name, NULL, count(distinct id) as ids_2, date 
       from table2  
       group by st_name, date
      )
     ) 
group by name, date
having sum(ids_1) = sum(ids_2)