连接
/*
第三章：表的连接
*/
3.1 内连接
use sql_store;
select order_id, o.customer_id, first_name, last_name
from orders o # 使用缩写
join customers c # 根据 customer_id 连接
on o.customer_id = c.customer_id
order by order_id;
select order_id, oi.product_id, quantity, oi.unit_price
from order_items oi
join products p
on oi.product_id = p.product_id;
3.2 跨数据库连接
select *
from order_items oi
join sql_inventory.products p # 不在current db里的需要加前缀
on oi.product_id = p.product_id;
## 3.3 自连接 self join
use sql_hr;
select e.employee_id,e.first_name, m.first_name as manager_name
from employees e
join employees m # 员工之间的从属关系
on e.reports_to = m.employee_id;
3.4 多表连接
use sql_store;
select
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name as status
from orders o
join customers c
on o.customer_id = c.customer_id
join order_statuses os
on os.order_status_id = o.status
order by o.order_id;
use invoicing;
select payment_id,date, amount,
c.name as customer_name,
pm.name as payment_method
from payments p
join clients c
on p.client_id = c.client_id
join payment_methods pm
on pm.payment_method_id = p.payment_method
order by p.date desc;
3.5 复合连接条件
use sql_store;
情况（复合主键）:每一个订单有多个产品，需要用订单+产品两列来作为唯一标示（复合主键）
select *
from order_items oi
join order_item_notes oin
on oi.order_id = oin.order_id
and oi.product_id = oin.product_id;
3.6 隐式连接
其实就是 全连接/交叉连接 + where条件
最好不要用，还是用Join
select *
from orders o, customers c
where o.customer_id = c.customer_id;
3.7 外连接
/*
join：inner join
left / right join ：outer join
*/
select # 这是一个inner join
c.customer_id,
c.first_name,
o.order_id
from customers c
join orders o
on o.customer_id = c.customer_id
order by c.customer_id; # 只有部分customers（有order的）
select # 这是一个outer join
c.customer_id,
c.first_name,
o.order_id
from customers c left join orders o # c中所有customer都会返回
on o.customer_id = c.customer_id # on里的条件只对o起作用
order by c.customer_id;
select # 练习
p.product_id,
p.name,
oi.quantity
from products p
left join order_items oi
on p.product_id = oi.product_id;
3.8 多表外连接
多次连接最好只用 left join 方便第三人理解
select # 这是一个outer join
c.customer_id,
c.first_name as customer_name,
o.order_id,
o.shipper_id,
sh.name as shipper_name
from customers c
left join orders o
on o.customer_id = c.customer_id
left join shippers sh # 多次外连接
on sh.shipper_id = o.shipper_id
order by c.customer_id;
3.9 自外连接
use sql_hr;
select
e.employee_id,
e.first_name,
m.first_name as manager_name
from employees e
left join employees m # 老板没有reports_to，leftjoin才能出现
on e.reports_to = m.employee_id;
3.10 using——简化join
但是只有列名相同之后才能用！
use sql_store;
select
o.order_id,
c.first_name,
sh.name as shipper_name
from orders o
join customers c
using (customer_id)
join shippers sh
using (shipper_id);
select * # 前面那个复合主键的问题如何用using
from order_items oi
join order_item_notes oin
-- on oi.order_id = oin.order_id
-- and oi.product_id = oin.product_id;
using (order_id, product_id);
use invoicing;
select
p.date,
c.name as client_name,
p.amount,
pm.name as payment_method
from payments p
join clients c
using (client_id)
join payment_methods pm
on pm.payment_method_id = p.payment_method;
3.11 自然连接，自动把相同col作为合并条件
use sql_store;
select
o.order_id,
c.first_name
from orders o
natural join customers c;
3.12 交叉连接
select
c.first_name as customer,
p.name as product
-- from customers c, products p # 方法1
from customers c
cross join products p # 方法2. 都不需要条件
order by first_name;
3.13 union 行的combine
select
order_id,
order_date,
'active' as status
from orders
where order_date >= '2019-01-01'
union # 行的combine
select
order_id,
order_date,
'archived' as status
from orders
where order_date < '2019-01-01';
select
customer_id,
first_name,
points,
'Gold' as type
from customers
where points > 3000
union
select
customer_id,
first_name,
points,
'Silver' as type
from customers
where points <= 3000 and points > 2000
union
select
customer_id,
first_name,
points,
'Bronze' as type
from customers
where points <= 2000;