Mysql8.0学习笔记（2）—— inner join，outer join，union

inner join(join)关键字

用于将多个表联合显示出来
除了之前的customers表来表示客户，同样有一张order表示客户订单,如下：

• 以某一列为值联合

select * 
from orders
inner join customers 				//为内部联合，inner关键字可省略
	on orders.customer_id = customers.customer_id //只联合customer_id相等的数据

• 联合数据库中的其它表

select order_id, o.customer_id,first_name,last_name	//对于两个都有的customer_id要指明主表
from orders o 	//令order简称为o
join customers  c//令customers简称为c			
	on o.customer_id = c.customer_id

• 联合其它数据库中的表

select * 
from order_items oi
join sql_inventory.products p	//需要为不是当前数据库的表加上前缀，前缀为其他数据库别名
	on oi.product_id = p.product_id

• 联合自身
  这种情况是为了一张表内关联数据的对比来显示，例如以下一张员工表
  
  员工要向经理汇报（reports_to）,而经理本身来说也是一名员工有自己的员工id，将每个员工向经理汇报的数据显示出来

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

• 联合两张及以上的表格
  在例子中将三张表order，customer，order_statuses联合起来显示有用的数据方便观察

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

执行显示图案如下，信息显示十分清楚，多张表的联合类似

• 对于复合主键的join
  如下图
  
  无论是order还是product_id都不能标识唯一一行数据，在表中以这两个键值做为复合主键来标识唯一数据，可以看到黄色的记号，一般表明为主键（一般为第一列），可以根据主键查找唯一数据，而这张表将order_id与product_id做为复合主键来查找数据。
  

use sql_store;
select *
from order_item_notes oin
join order_items oi
	on oi.order_id = oin.order_id
    and oi.product_id = oin.product_id	//复合主键的联合

outer join

有名为外部联机，有时候会出现以下情况。有两张表customer，order

order
customer
通过如下代码

SELECT
	c.customer_id,
    c.first_name,
    o.order_id
from customers c
join orders o
	on c.customer_id = o.customer_id
order by c.customer_id

此时显示的只有条件匹配的数据，但是想要无论order中有没有该客户，都要显示出来，就要用到外部联合

• 外部联合left join与right join
  无论on的条件是否成立，使用left join联合时前面的表都能全部显示。使用right join联合时后面的表都全部显示。

SELECT
	c.customer_id,
    c.first_name,
    o.order_id
from customers c
left (outer) join orders o	//outer可以省略
	on c.customer_id = o.customer_id
order by c.customer_id

此时能够显示所有的客户id，尽管没有order_id

• 外部联合的多表联合

SELECT
	c.customer_id,
    c.first_name,
    o.order_id
    sh.name as shipper
from customers c
left (outer) join orders o	//outer可以省略
	on c.customer_id = o.customer_id
left join shippers sh
	on o.shipper_id = sh.shipper_id
order by c.customer_id

尽量使用单一的left或者rignt，不要混合使用

• self outer join
  例如之前的内部自身联合的员工表，对其进行下修改

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

manager的员工编号能够显示出来。

• 使用using代替join中的on，只有在两个判断条件键值同名时才可以使用

SELECT
	c.customer_id,
    c.first_name,
    o.order_id
    sh.name as shipper
from customers c
left (outer) join orders o	//outer可以省略
	 //on c.customer_id = o.customer_id
	using(cuntomer_id)	//效果等同		   
left join shippers sh
	//on o.shipper_id = sh.shipper_id
	using(shipper_id )	
order by c.customer_id

• using在复合主键的使用

use sql_store;
select *
from order_item_notes oin
join order_items oi
	//on oi.order_id = oin.order_id and oi.product_id = oin.product_id
	using(order_id ,product_id)

• natural join(不建议使用)

select 
	o.order_id
	c.first_name
from orders o
natural join customers c	//基于两个表的公共列，因此在大量的表中不可控，不建议使用

• cross join
  将第一张表中的每行与第二张表中的每行组合，所以一共显示的行数为 table1行数 * table2行数

select 
	c.first_name as customer,
	p.name as product
from customers c
cross join products p	
//from customers c,orders o 隐式写法
order by c.first_name

union

将两次查询的结构合并出来显示，可以在同一个表内也可以在不同的表里

selec first_name	//注意选择的列数的数量要一致
from customers
union
select name
from shipper 

• 以customer为例，points在2000以下的打上青铜标签，2000-3000之间的打上白银标签，3000以上的打开黄金标签

use sql_store;
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

结果如下