假设有两种表:test_on_position表和address表,address表存放地址,test_on_position存放会员及其常用的地址,数据如下:
address表:
test_on_position表:
1. left join
条件在where后面
SELECT
*
FROM
test_on_position
LEFT JOIN address ON test_on_position.address = address.id
WHERE
test_on_position.address IS NOT NULL
运行结果:
条件在on后面
SELECT
*
FROM
test_on_position
LEFT JOIN address ON test_on_position.address = address.id AND test_on_position.address IS NOT NULL
运行结果:
结果分析
当条件放在on后面时,无论on条件的真假,都会返回左表的值;
当条件放在where后面时,只有满足条件的记录会返回。
inner join
条件在on后面
SELECT
*
FROM
test_on_position
INNER JOIN address ON test_on_position.address = address.id AND test_on_position.address IS NOT NULL
运行结果:
条件在where后面
SELECT
*
FROM
test_on_position
INNER JOIN address ON test_on_position.address = address.id
WHERE
test_on_position.address IS NOT NULL
运行结果:
结果分析
无论条件放在哪里,都只会返回满足条件的结果。
原文:https://www.cnblogs.com/acode/p/9531755.html