postgresql联合查询、连接查询

 

• 联合查询

select  * from cw.cities c,(select state, MAX(pop) max  FROM cw.cities  group by state) t  where t.state = c.state and t.max=c.pop

 

 

select  c.* from cw.cities c,(select state, MAX(pop) max  FROM cw.cities  group by state) t  where t.state = c.state and t.max=c.pop

 

 

 

• left join on and

select  c.* from cw.cities c left join (select state, MAX(pop) max  FROM cw.cities  group by state) t  on t.state = c.state and t.max=c.pop

  

 

 

• left join on where

 

select  c.* from cw.cities c left join (select state, MAX(pop) max  FROM cw.cities  group by state) t  on t.state = c.state where t.max=c.pop

  

 

 

• inner join on and

 

select  c.* from cw.cities c inner join (select state, MAX(pop) max  FROM cw.cities  group by state) t  on t.state = c.state and t.max=c.pop