NOT EXISTS 与 NOT IN 与 LEFT JOIN WHERE IS NULL 之间有什么区别? [英] What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?
问题描述
在我看来,您可以使用 NOT EXISTS、NOT IN 或 LEFT JOIN WHERE IS NULL 在 SQL 查询中执行相同的操作.例如:
It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example:
SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)
SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL
我不确定我的所有语法是否正确,但这些是我见过的一般技术.为什么我会选择使用一种而不是另一种?性能有区别吗...?其中哪一个是最快/最有效的?(如果这取决于实现,我什么时候使用每一个?)
I'm not sure if I got all the syntax correct, but these are the general techniques I've seen. Why would I choose to use one over the other? Does performance differ...? Which one of these is the fastest / most efficient? (If it depends on implementation, when would I use each one?)
推荐答案
NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:SQL Server
NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:PostgreSQL
NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:Oracle
NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:MySQL
简而言之:
NOT IN
有点不同:如果列表中只有一个NULL
,它永远不会匹配.NOT IN
is a little bit different: it never matches if there is but a singleNULL
in the list.在
MySQL
中,NOT EXISTS
效率稍低
在
SQL Server
中,LEFT JOIN/IS NULL
效率较低在
PostgreSQL
中,NOT IN
效率较低在
Oracle
中,三种方法都是一样的.In
Oracle
, all three methods are the same.这篇关于NOT EXISTS 与 NOT IN 与 LEFT JOIN WHERE IS NULL 之间有什么区别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!