内连接
join默认是自然连接on表示在什么条件下连接
sql
复制代码
-- 有重复的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
简写表名称
sql
复制代码
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
sql
复制代码
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 的表前面加上是哪一个数据库名字就可以。 查询语句会跟着当前使用的数据库的改变而改变
sql
复制代码
select * from sql_store.order_items oi join products p on oi.product_id = p.product_id
自连接
sql
复制代码
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 |
多表连接
sql
复制代码
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 |
sql
复制代码
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 |
复合主键
需要使用多重条件来连接两个主键
sql
复制代码
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;
隐式链接
隐式链接是上面的显式连接的另一种写法。 但是不推荐这样写，因为一旦忘记写条件，就会得到两张表的积
sql
复制代码
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外连接
sql
复制代码
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同理。
多表外连接
sql
复制代码
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
sql
复制代码
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 |
自外连接
就是对自己使用外连接，可以有不满足条件的地方
sql
复制代码
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
sql
复制代码
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)
sql
复制代码
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自动检索同名列作为合并条件。 最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的
sql
复制代码
USE sql_store; SELECT o.order_id, c.first_name FROM orders o NATURAL JOIN customers c
交叉连接
得到名字和产品的所有组合，因此不需要合并条件。显式连接：
sql
复制代码
select c.first_name, p.name from customers c cross join products p order by c.first_name
隐式连接：
sql
复制代码
select sh.name as shipper, p.name as product from shippers sh,products p order by sh.name
Union联合
结合多个表的行FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表
sql
复制代码
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'
sql
复制代码
SELECT first_name, last_name FROM customers UNION SELECT nameFROM shippers
注意：
- 合并的查询结果必须列数相等，否则会报错
- 合并表里的列名由排在 UNION 前面的决定
sql
复制代码
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
