mysql详解2：连接

join
join on 连接

自连接 查询每个员工的主管名字
select
e.employee_id,
e.first_name,
m.first_name manager_name
from employees e
Join employees m
on e.reports_to =m.employee_id;

 

多表连接
SELECT
o.order_id,
o.order_date,
c.forst_name,
os.NAME status_name
FROM
orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Order_status os ON o.STATUS = os.order_status_id

 

 

复合连接条件
select * from order_item oi
join order_item_notes oin
on oi.order_id =oin.order_id
and oi.product_id =oin.product_id

 

内连接
两个表的公共部分
显示内连接 与 隐式内连接

select *
from orders o ,customers c
where o.customer_id =c.customer_id 隐式内连接

SELECT *
FROM orders o
JOIN customer c
ON o.customer_id = c.customer_id 显式内连接

外连接
左外连接 左表的所有记录都会被返回
右外连接 右表的所有记录都会被返回
SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name as shipper
FROM orders o
left JOIN customer c
ON o.customer_id = c.customer_id
left JOIN shippers sh
on o.shipper_id=sh.shhipper_id
order by c.customer_id

多表外连接
select
o.order_id,
o.order_date,
c.first_name as customer,
os.status
from orders o
join customers c
on o.customer_id =c.customer_id
left join shipper sh
on o.shipper_id =sh.shipper_id
join order_statuss os
on o.status =os.order_status_id

自外连接
显示出没有上级的记录出来
select *
from employees e
left join employees m
on e.reports_to =m.employee_id

 

using 使用条件 两个表中关联字段名字相同
select
o.order_id
from orders o
join customers c
-- on o.customer_id=c.customer_id
using(customer_id)

selct *
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)

 

自然连接（不推荐）
select
o.order_id,
c.first_name
from orders o
NATURAL Join customers c

交叉连接 相当于排列组合C乘 又相当于X乘 如尺码X颜色
select
c.first_name customer,
p.name product
from customers c
cross join products p
order by c.first_name ;

 

交叉连接的隐式查询
select
c.first_name customer,
p.name product
from customers c， products p
order by c.first_name ;

 

联合：两段查询联合在一起
两个查询的列需要相同 第一段查询决定列名

生成新的列
select customer_id,first_name,points,'Bronze' as type
from customers
where points< 2000
ORDER BY first_name