INNER JOIN和LEFT JOIN中的坑

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

---