SQL 语句（四）

• join

当存在多个table时，用join on 把table 联系在一起

select count(*)from newspaper;


select count(*)from online;

select count(*)
from newspaper
join online
on newspaper.id=online.id;

• left join（一个表中的和另一表的交集为null）

• select *
  from newspaper
  left join online
  on newspaper.id= online.id;
  
  select *
  from newspaper
  left join online
  on newspaper.id= online.id
  where online.id is null;

   

• cross join（左右表）

•   
  SELECT *
  FROM newspaper
  CROSS JOIN months;
  
  SELECT *
  FROM newspaper
  CROSS JOIN months
  WHERE start_month <= month 
    AND end_month >= month;
  
  SELECT month, 
    COUNT(*)
  FROM newspaper
  CROSS JOIN months
  WHERE start_month <= month 
    AND end_month >= month
  GROUP BY month;

   

• union（上下表）

• select *
  from newspaper
  union
  
  select *
  from online;
  
   

   

• with (一个表的计算结果是另一个表的一项)

• WITH previous_query AS (
     SELECT customer_id,
        COUNT(subscription_id) AS 'subscriptions'
     FROM orders
     GROUP BY customer_id
  )
  SELECT customers.customer_name, 
     previous_query.subscriptions
  FROM previous_query
  JOIN customers
    ON previous_query.customer_id = customers.customer_id;