MySQL中条件放在where后面与放在on后面的区别
假设有两种表: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
运行结果:
结果分析
无论条件放在哪里,都只会返回满足条件的结果。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步