這兩種 SQL 風格有什麼區別？

如果我的 Redshift/Postgresql 中有一個名為 conn_log 的簡單表

從性能上看，這兩個命令有什麼區別？

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;

第二個查詢：

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 

INNER JOIN...ON至於vs子句的區別，這裡WHERE有一個很好的答案。那裡有幾個答案，接受的答案幾乎總結了這一切。

但是，我不得不評論說，您的查詢可以重寫以顯著提高性能，如下所示：

select wifi_name
     ,sum(case when success = 1 then 1 else 0 end)*100/count(*) as success_rate
from conn_log 
group by wifi_name;

在 PostgreSQL 9.4+ 中，它更簡單：

select
   wifi_name, 
   count(*) filter (where success = 1) / count(*) * 100 as success_rate
from conn_log 
group by wifi_name;

引用自：https://dba.stackexchange.com/questions/97760