MySQL中条件放在where后面与放在on后面的区别

假设有两种表:test_on_position表和address表,address表存放地址,test_on_position存放会员及其常用的地址,数据如下:

address表:

MySQL中条件放在where后面与放在on后面的区别

test_on_position表:

MySQL中条件放在where后面与放在on后面的区别

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

运行结果:

MySQL中条件放在where后面与放在on后面的区别

条件在on后面

SELECT
    *
FROM
    test_on_position
LEFT JOIN address ON test_on_position.address = address.id AND test_on_position.address IS NOT NULL

运行结果:

MySQL中条件放在where后面与放在on后面的区别

结果分析

当条件放在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

运行结果:

MySQL中条件放在where后面与放在on后面的区别

条件在where后面

SELECT
    *
FROM
    test_on_position
INNER JOIN address ON test_on_position.address = address.id
WHERE
    test_on_position.address IS NOT NULL

运行结果:

MySQL中条件放在where后面与放在on后面的区别

结果分析

无论条件放在哪里,都只会返回满足条件的结果。

MySQL中条件放在where后面与放在on后面的区别

上一篇:初识MySQL


下一篇:【mysql锁】for update定义