If I have a simple table named: conn_log in my Redshift/Postgresql
In the performance of view, what difference between these two commands ?
select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
from conn_log
group by wifi_name) t1,
(select wifi_name, count(*) as sconn
from conn_log
where success = 1
group by wifi_name) t2
where t1.wifi_name = t2.wifi_name;
second query:
select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
from conn_log
group by wifi_name) t1
join
(select wifi_name, count(*) as sconn
from conn_log
where success = 1
group by wifi_name) t2
on t1.wifi_name = t2.wifi_name
Best Answer
As for the difference between
INNER JOIN...ON
vsWHERE
clause, there is a good answer here. There are several answers there and the accepted answer pretty much summarises it all.However, I cannot but comment that you query can be rewritten to significantly improve the performance, like this:
In PostgreSQL 9.4+, it is even simpler: