Join从句

引用两个数据库举例说明：

 

 

1.内连接

select a.name,a.do,b.name,b.do
from person a INNER JOIN friend b
on a.name = b.name;

 

2.全连接

select a.name,a.do,b.name,b.do
from person a LEFT JOIN friend b
on a.name = b.name
UNION ALL
select a.name,a.do,b.name,b.do
from person a RIGHT JOIN friend b
on a.name = b.name;

 

3.左连接

select a.name,a.do,b.name,b.do
from person a LEFT JOIN friend b
on a.name = b.name;

 4.右连接

select a.name,a.do,b.name,b.do
from person a RIGHT JOIN friend b
on a.name = b.name;

 5.交叉连接

select a.name,a.do,b.name,b.do
from person a CROSS JOIN  friend b;

 下面是很有意思的两个使用技巧：

1.更新表

UPDATE person a JOIN(
select b.name
from person a INNER JOIN friend b
on a.name=b.name
) b on a.name = b.name
 set a.do = "test";

 2.优化子查询

优化前：
select a.name,a.do,(
select do from friend b
where a.name = b.name
) as do2
from person a;

优化后：
select a.name,a.do,b.do as do2
from person a LEFT JOIN friend b
on a.name = b.name;

今天是2021年11月7日，立冬，济南迎来了2021年的第一场大雪，开心😁😁