mysql：[内]连接、左[外]连接、右[外]链接、[全]外连接、自连接

0.

https://blog.csdn.net/a23452/article/details/121367860

1.

1. 以下参考https://blog.csdn.net/weixin_46273997/article/details/112977917
2. “自左连接”参考https://www.yzktw.com.cn/post/968442.html
3. 表的自关联是通过把一个表命名不同的名称，然后找到想要关联的关系进行关联（看成两张表进行处理）
4. 左连接left join查询出来的结果不仅仅是on 后面条件关联的数据，还包括条件中没有关联到的但是左边的select查询到的结果，没有关联到的数据部分用NULL填充

# 左主表dish菜品，右category类别


# 1、内连接：两张表的共同数据
SELECT d.id as did, d.`name` as dname, d.category_id, c.id as cid, c.`name` as cname 
from dish as d 
JOIN category as c on d.category_id=c.id


# 内连接相当于：
SELECT d.id as did, d.`name` as dname, d.category_id, c.id as cid, c.`name` as cname
FROM dish as d, category as c
WHERE d.category_id=c.id


# 2、左外连接：左右交集+左私有
select d.id as did, d.`name` as dname, d.category_id, c.id as cid, c.`name` as cname
from dish as d
left join category as c on d.category_id=c.id


# 3、右外连接：左右交集+右私有
select d.id as did, d.`name` as dname, d.category_id, c.id as cid, c.`name` as cname
from dish as d
right join category as c on d.category_id=c.id


# 4、全外连接：左右交集+左私有+右私有
# select * from a full outer join b on a.key = b.key; 此种方式只能oracle用；mysql不适用上述，得用union
select * from dish d left join category c on d.category_id=c.id
union
select * from dish d right join category c on d.category_id=c.id
 
 
# 5. 左私有
select * from dish d left join category c on d.category_id=c.id where c.id is null
 
 
# 6. 右私有
select * from dish d right join category c on d.category_id=c.id where d.category_id is null

 
# 7. 左私有+右私有（即"全外连接"去掉交集部分）
# select * from a full outer join b on a.key = b.key where a.key is null or b.key is null; 此种方式只能oracle用；mysql不适用上述，得用union
select * from dish d left join category c on d.category_id=c.id where c.id is null
union
select * from dish d right join category c on d.category_id=c.id where d.category_id is null

2.

https://blog.csdn.net/weixin_46273997/article/details/112977917
https://blog.csdn.net/DianLanHong/article/details/119766952
https://blog.csdn.net/luoxuexi2020/article/details/120656993
join和whereand的区别改写https://blog.csdn.net/m0_61607827/article/details/124693841
https://blog.csdn.net/u013372487/article/details/52622491
https://zhidao.baidu.com/question/1802810586723319507.html
left join on 后的 and 条件与 where 中条件的区别https://blog.csdn.net/dd2016124/article/details/125349429
子查询与joinhttps://blog.csdn.net/u010711495/article/details/118558808

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

等价于

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName