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
我不确定所有语法是否正确,但这是我所见过的常规技术。我为什么选择一个使用另一个?性能会有所不同吗?哪一个是最快/最有效的? (如果取决于实现,我何时会使用每个?)
#1 楼
NOT IN与不存在vs.左连接/为NULL:SQL Server
NOT IN与不存在vs.左连接/为NULL:PostgreSQL
NOT IN与不存在vs.不存在与。LEFT JOIN / IS NULL:Oracle
不存在vs不存在vs. LEFT JOIN / IS NULL:MySQL
概括地说:
NOT IN
是一个有点不同:如果列表中只有一个NULL
,则永远不会匹配。在
MySQL
中,NOT EXISTS
的效率要低一些。高效在
SQL Server
中,LEFT JOIN / IS NULL
效率较低在
PostgreSQL
中,这三种方法都相同。评论
感谢您的链接!并感谢您的快速概述...我的办公室由于某些原因而阻止了该链接:P,但只要我连接到常规计算机,便会立即检查出来。
–花花公子
2010-2-11在18:47
另一点是,如果table1 .a包含NULL,则EXISTS查询将不返回此行,但如果table2为空,则NOT IN查询将返回该行。 NOT IN与NOT EXISTS可空列:SQL Server
–马丁·史密斯
2012年6月19日在7:29
@MartinSmith:NULL NOT IN()的计算结果为true(非NULL),就像NOT EXISTS(NULL =列)一样
– Quassnoi
2012年6月19日7:33
@Quassnoi-嗯,好点了,弄错了方向。 NOT EXISTS将始终返回该行,但NOT IN仅在子查询不返回任何行时才返回。
–马丁·史密斯
2012年6月19日7:37
#2 楼
如果数据库擅长优化查询,则前两个会转换为接近第三个的结果。对于诸如您所询问的情况这样的简单情况,应该几乎没有差异,因为它们都将作为联接执行。在更复杂的查询中,数据库可能无法从
not in
和not exists
查询中进行联接。在这种情况下,查询速度会慢很多。另一方面,如果没有可以使用的索引,则联接也可能会执行不佳,因此,仅仅因为您使用联接并不意味着您是安全的。您将必须检查查询的执行计划,以判断是否可能存在性能问题。#3 楼
假设您避免使用空值,那么它们都是使用标准SQL编写反联接的所有方法。一个明显的省略是使用
EXCEPT
的等效项:SELECT a FROM table1
EXCEPT
SELECT a FROM table2
请注意,在Oracle中,您需要使用
MINUS
运算符(可以说是更好的名称):SELECT a FROM table1
MINUS
SELECT a FROM table2
说到专有语法,也可能存在非标准语法根据您使用的产品,值得研究的等效产品,例如SQL Server中的
OUTER APPLY
(类似):SELECT t1.a
FROM table1 t1
OUTER APPLY
(
SELECT t2.a
FROM table2 t2
WHERE t2.a = t1.a
) AS dt1
WHERE dt1.a IS NULL;
#4 楼
当需要在具有多字段主键的表中插入数据时,考虑到不检查“表中不存在带有'此类'值的记录”会更快(我在Access中尝试过,但我认为在任何数据库中), -只需插入表中,多余的记录(通过键)将不会被插入两次。#5 楼
从性能角度来看,始终避免使用诸如NOT IN,NOT EXISTS等反向关键字,...因为要检查DBMS需要遍历所有可用项的反向项目,然后删除反向选择。
评论
当您实际上不需要时,您提出什么解决方法?
–牙齿
16年4月6日在7:57
好吧,当没有原因的选择时,我们需要使用NOT运算,这就是它们存在的原因。最佳做法是在我们有其他替代解决方案时避免使用它们。
– Lahiru Cooray
16年6月13日在3:47
@onedaywhen,如果优化程序转换查询并返回错误结果,则为错误
– DavidדודוMarkovitz
16-10-9在10:12
@DuduMarkovitz:是的,如果您与SQL Server团队联系,并且他们承认该错误但拒绝修复,因为他们说这样做可能会使查询的运行速度变慢,那么这是您需要处理的错误。
–有一天
16-10-10在7:27
@onedaywhen-我认为这不是一种假设的情况:-)您是否还记得错误的详细信息?
– DavidדודוMarkovitz
16-10-10在18:09
评论
许多常见的SQL引擎使您能够查看执行计划。通过这种方式,对于逻辑上等效的查询,您通常可以发现效率上的显着差异。任何方法的成功都取决于诸如表大小,存在哪些索引等因素。@wich:没有数据库关心您在EXISTS子句中返回的确切内容。您可以返回*,NULL或其他任何值:所有这些都将被优化。
@wich-为什么?两者都在这里:techonthenet.com/sql/exists.php和这里:msdn.microsoft.com/en-us/library/ms188336.aspx似乎都使用* ...
@wich:这与“表达兴趣”无关。这与查询解析器要求您在SELECT和FROM之间放置一些内容有关。和*只是更容易键入。是的,SQL确实与自然语言有些相似,但是它是由一台机器(一台编程的机器)解析和执行的。这并不是说它会突然闯入您的小隔间并大喊“停止对EXISTS查询中的多余字段的要求,因为我讨厌解析它们然后扔掉它们!”。真的可以用电脑。
@Quassnoi,如果您仅出于解释机器的目的而编写代码,则该代码看起来会很恐怖,不幸的是,很多人都这样工作。但是,如果您用另一种语言编写代码,编写代码以表达您希望机器作为对等体的公报,那么您将编写更好,更可维护的代码。要聪明,为人而不是为计算机编写代码。