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 single NULL 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屋！