本文主要是介绍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
这篇关于INNER JOIN和LEFT JOIN中的坑的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
