海伦君SQL进阶教程笔记2

内连接-多表查询

--内连接
SELECT *
FROM oders
JOIN customers 
ON oders.customer_id=customers.customer_id --通过id相同连接订单和顾客列表
-------------------------------------------------
--跨数据库连接
select * 
From order_items oi
JOIN sql_inventory.products p
 ON oi.product_id=p.product_id
--------------------------------------
--自连接
select *
FROM order_items oi
JOIN order_item_notes oin
     ON oi.order_id=oin.order_id
     AND oi.product_id=oin.product_id
----------------------------------------------------
--多表连接
     SELECT
o.order_id,
o.order_date,
c.first_name as customer,
sh.name as shipper,
os.name as status
FROM orders o 
JOIN customers c
	ON o.customer_id=c.customer_id
left join shippers sh
	on o.shipper_id=sh.shipper_id
 join order_statuses os
	on o.status=os.order_status_id
--------------------------------------------------------
交叉连接
SELECT
s.name as name,
p.name as product
FROM shippers s
cross join products p
order by s.name
----------------------------------------------------
联合
SELECT
customer_id,
first_name,
points,
'bronze' as type
from customers 
where points<2000
union
select
customer_id,
first_name,
points,
'silver' as type
from customers 
where points between 2000 and 3000
union
select
customer_id,
first_name,
points,
'gold' as type
from customers 
where points>3000
order by first_name
-----------------------------------------------