内连接
join默认是自然连接
on表示在什么条件下连接
-- 有重复的customer_id需要确定是哪一个，不然会因为重复而报错
select order_id,orders.customer_id, first_name, last_name
from orders
inner join customers -- inner可以省略
on orders.customer_id = customers.customer_id
简写表名称
select order_id,a.customer_id, first_name, last_name
from orders a
inner join customers -- inner可以省略
on a.customer_id = customers.customer_id
自然连接 oi 和 p 表中 oi.product_id = p.product_id 的部分，并输出 order_id, oi.product_id, quantity, oi.unit_price
select order_id,oi.product_id, quantity,oi.unit_price
from order_items oi
join products p
on oi.product_id = p.product_id;
跨数据库连接
同理啊，只需要在 join 的表前面加上是哪一个数据库名字就可以。 查询语句会跟着当前使用的数据库的改变而改变
select *
from sql_store.order_items oi
join products p
on oi.product_id = p.product_id
自连接
use sql_hr;
select
e.employee_id,
e.first_name,
m.first_name AS manager
from employees e
join employees m
on e.reports_to = m.employee_id
和自己链接，通过每个员工编号和领导编号可以输出如下信息。
| employee_id | first_name | manager |
|---|---|---|
| 33391 | D'arcy | Yovonnda |
| 37851 | Sayer | Yovonnda |
| 40448 | Mindy | Yovonnda |
| 56274 | Keriann | Yovonnda |
| 63196 | Alaster | Yovonnda |
多表连接
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 o.status = os.order_status_id
| 8 | 2018-06-08 | Clemmie | Betchley | Processed |
| 6 | 2018-11-18 | Levy | Mynett | Processed |
| 4 | 2017-01-22 | Ines | Brushfield | Processed |
| 3 | 2017-12-01 | Thacher | Naseby | Processed |
| 1 | 2019-01-30 | Elka | Twiddell | Processed |
use sql_invoicing;
select
p.date,
p.invoice_id,
c.client_id,
c.name,
pm.name
from payments p
join clients c
on c.client_id = p.client_id
join payment_methods pm
on pm.payment_method_id = p.payment_method;
| 2019-02-12 | 2 | 5 | Topiclounge | Credit Card |
| 2019-01-03 | 6 | 1 | Vinte | Credit Card |
| 2019-01-11 | 11 | 3 | Yadel | Credit Card |
| 2019-01-26 | 13 | 5 | Topiclounge | Credit Card |
| 2019-01-15 | 15 | 3 | Yadel | Credit Card |
| 2019-01-15 | 17 | 3 | Yadel | Credit Card |
复合主键
需要使用多重条件来连接两个主键
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;
隐式链接
隐式链接是上面的显式连接的另一种写法。 但是不推荐这样写，因为一旦忘记写条件，就会得到两张表的积
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;
-- Implpicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
外连接
默认只写join是内连接，只会返回满足 on 条件的记录
如果还想显示未满足的记录，在join前加上left或者right，就是outer外连接
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
使用LEFT JOIN，customers 中的所有记录都会被返回，不管满不满足条件。
RIGHT JOIN同理。
多表外连接
SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
left Join shippers sh
on o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
SELECT
o.order_date,
o.order_id,
c.first_name,
sh.name as shipper,
os.name as status
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
left Join shippers sh
on o.shipper_id = sh.shipper_id
Join order_statuses os
on o.status = os.order_status_id
ORDER BY status
| 2018-06-08 | 8 | Clemmie | Processed | |
| 2018-11-18 | 6 | Levy | Processed | |
| 2017-01-22 | 4 | Ines | Processed | |
| 2017-12-01 | 3 | Thacher | Processed | |
| 2019-01-30 | 1 | Elka | Processed | |
| 2018-04-22 | 10 | Elka | Schinner-Predovic | Shipped |
| 2017-07-05 | 9 | Levy | Hettinger LLC | Shipped |
| 2018-09-22 | 7 | Ines | Mraz, Renner and Nolan | Shipped |
| 2017-08-25 | 5 | Clemmie | Satterfield LLC | Shipped |
| 2018-08-02 | 2 | Ilene | Mraz, Renner and Nolan | Shipped |
自外连接
就是对自己使用外连接，可以有不满足条件的地方
use sql_hr;
SELECT
e.employee_id ,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m
ON e. reports_to = m. employee_id
USING子句
当作为合并条件（join condition）的列在两个表中有相同的列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简化，内/外链接均可如此简化。
但是列名不同就必须用 ON
use sql_store;
SELECT
o.order_id ,
c.first_name ,
sh.name AS shipper
FROM orders o
JOIN customers c
USING(customer_id)
LEFT JOIN shippers sh
USING (shipper_id)
select *
from order_items oi
left join order_item_notes oin
-- on oi.order_id = oin.order_id and
-- oi.product_id = oin.product_id
using (order_id,product_id)
自然连接
就是让MySQL自动检索同名列作为合并条件。 最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的
USE sql_store;
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
交叉连接
得到名字和产品的所有组合，因此不需要合并条件。 显式连接：
select
c.first_name,
p.name
from customers c
cross join products p
order by c.first_name
隐式连接：
select
sh.name as shipper,
p.name as product
from shippers sh,products p
order by sh.name
Union联合
结合多个表的行
FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表
select
order_id,
order_date,
'Active' as status
from orders
where order_date >= '2019-01-01'
union -- 联合了上下两个的查询结果
select
order_id,
order_date,
'Archived' as status
from orders
where order_date < '2019-01-01'
SELECT first_name, last_name
FROM customers
UNION
SELECT nameFROM shippers
注意：
-
合并的查询结果必须列数相等，否则会报错
-
合并表里的列名由排在 UNION 前面的决定
select
customer_id,
first_name,
points,
'Bronze' as type
from customers
where points < 2000
union
select
customer_id,
first_name,
points,
'Sliver' as type
from customers
where points >= 2000 and points < 3000
union
select
customer_id,
first_name,
points,
'Gold' as type
from customers
where points >= 3000
order by points desc
