INNER JOIN和LEFT JOIN中的坑

最新推荐文章于 2025-04-09 17:16:26 发布

原创最新推荐文章于 2025-04-09 17:16:26 发布 · 1.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#inner join #left join

SQL 专栏收录该内容

8 篇文章

订阅专栏

博客围绕a表和b表，介绍了简单的inner join和left join，还提及带b表条件、双表条件的inner join和left join，并对部分情况进行了效率比较，但后续效率内容待补。

a表和b表

在这里插入图片描述

简单的inner join

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
INNER JOIN b on b.id=a.id

在这里插入图片描述

简单的left join

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
LEFT JOIN b on b.id=a.id

在这里插入图片描述

带b表条件的inner join

以下两个sql结果一样

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
INNER JOIN b on b.id=a.id
where b.state =1

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
INNER JOIN b on b.id=a.id AND b.state =1

在这里插入图片描述

比较效率

后续补上

带双表条件的inner join

以下两个sql结果一样

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
INNER JOIN b on b.id=a.id
where a.state=1 and b.state =1

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
INNER JOIN b on b.id=a.id AND b.state =1
where a.state=1

在这里插入图片描述

比较效率

后续补上

带b表条件的left join

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
LEFT JOIN b on b.id=a.id
where b.state =1

在这里插入图片描述

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
LEFT JOIN b on b.id=a.id AND b.state =1

在这里插入图片描述

带双表条件的left join

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
LEFT JOIN b on b.id=a.id
where a.state=1 and b.state =1

在这里插入图片描述

SELECT a.id as aid, a.name as aname, a.state astate,
b.id as bid, b.name as bname, b.state bstate 
FROM a
LEFT JOIN b on b.id=a.id AND b.state =1
where a.state=1