inner join left join 什么情况效果相同

效果不同的情况

SELECT g.name AS groupName,
g.root_code AS rootCode,
g.data_sort AS groupDataSort,
l.*
FROM wise_system_point_group g
LEFT JOIN wise_system_point_list l ON g.code = l.group_code
WHERE  g.code =  "drug"
ORDER BY g.data_sort, l.data_sort
###########################
SELECT g.name AS groupName,
g.root_code AS rootCode,
g.data_sort AS groupDataSort,
l.*
FROM wise_system_point_group g
inner JOIN wise_system_point_list l ON g.code = l.group_code
WHERE  g.code =  "drug"
ORDER BY g.data_sort, l.data_sort
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

left join 结果：

inner join 结果：

结果相同的情况

SELECT g.name AS groupName,
g.root_code AS rootCode,
g.data_sort AS groupDataSort,
l.*
FROM wise_system_point_group g
LEFT JOIN wise_system_point_list l ON g.code = l.group_code
WHERE  g.code =  "drug" AND l.tenant_id = "52b6989827ee5aa026841868451124e9"
ORDER BY g.data_sort, l.data_sort
##########################
SELECT g.name AS groupName,
g.root_code AS rootCode,
g.data_sort AS groupDataSort,
l.*
FROM wise_system_point_group g
inner JOIN wise_system_point_list l ON g.code = l.group_code
WHERE  g.code =  "drug" AND l.tenant_id = "52b6989827ee5aa026841868451124e9"
ORDER BY g.data_sort, l.data_sort
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

结果均为无记录：

结论

由于左连接的右边表没有满足on条件的记录，所以查询出来的所有字段均为空。
此时若加一个右边表筛选条件，就无法满足，导致查询无记录。