SQL 必知必会第十三课 创建高级联结

SQL 必知必会第十三课 创建高级联结

##使用表别名
select concat(vend_name,'(',vend_country,')') as vend_title
from vendors
order by vend_name;

select cust_name, cust_contact
from customers as C, orders as O, OrderItems as OI
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = 'RGAN01';

## 自联结
select cust_id, cust_name, cust_contact
from customers
where cust_name = (select cust_name
				   from customers
				   where cust_contact = 'Jim Jones'); -- 子查询
                   
select c1.cust_id,c1.cust_name,c1.cust_contact
from customers as c1, customers as c2
where c1.cust_name = c2.cust_name
and c2.cust_contact = 'Jim Jones';      -- 使用联结

##自然联结
select C.*, O.order_num, o.order_date,
       oi.prod_id, oi.quantity, oi.item_price
from customers as C, orders as O, orderitems as OI
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'RGAN01';

## 外联结
select customers.cust_id, orders. order_num
from customers
inner join orders on customers.cust_id = orders.cust_id; -- 检索所有客户及其订单

select Customers.cust_id, orders.order_num
from customers
left outer join orders on customers.cust_id = orders.cust_id; -- 查询那些包括没有订单在内的所有顾客

## 使用带聚类函数的联结
select customers.cust_id,
	   count(orders.order_num) as num_ord
from customers
inner join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;

select customers.cust_id,
       count(orders.order_num) as num_ord
from customers
  left outer join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;

#challenges 
select customers.cust_name, orders.order_num
from  customers
inner join orders on customers.cust_id = orders.cust_id; -- 1

select customers.cust_name, orders.order_num
from customers
left outer join orders on customers.cust_id = orders.cust_id; -- 2

select products.prod_name, orderitems.order_num
from products
left outer join  orderitems on products.prod_id = orderitems.prod_id; -- 3 

select products.prod_name,
	count(orderitems.order_num) as sum_order
from products
left outer join  orderitems on products.prod_id = orderitems.prod_id
group by prod_name; -- 4

select vendors.vend_id,
	   count(products.vend_id) as supply_num
from vendors
left outer join products on vendors.vend_id = products.vend_id
group by vendors.vend_id; -- 5

注意点：

1. 使用联结比使用子查询快

2. 标准联结可能会使相同的列多次出现，自然联结使每一列只返回一次

3. 左联结和右联结实际上是一样的，mysql不支持full outer join