SELECT a,b,c
FROM a_table
WHERE EXISTS
(SELECT * -- This is what I normally write
FROM another_table
WHERE another_table.b = a_table.b
)
其他一些人写道：
SELECT a,c
FROM a_table
WHERE EXISTS
(SELECT 1 --- This nice '1' is what I have seen other people use
FROM another_table
WHERE another_table.b = a_table.b
)
当条件不是EXISTS而不是EXISTS时：在某些情况下,我可能会用LEFT JOIN和一个额外条件(有时称为antijoin)来编写它：
SELECT a,c
FROM a_table
LEFT JOIN another_table ON another_table.b = a_table.b
WHERE another_table.primary_key IS NULL
我试图避免它,因为我认为其含义不太明确,特别是当你的primary_key不是那么明显,或者当你的主键或你的连接条件是多列时(你很容易忘记其中一列).但是,有时你会维护其他人编写的代码……它就在那里.
>使用SELECT 1而不是SELECT *有什么区别(除了样式)？
是否存在不同行为的角落情况？
>虽然我写的是(AFAIK)标准sql：不同的数据库/旧版本是否存在这样的差异？
>明确写一个反连接有什么好处吗？
当代规划师/优化者是否会将其与NOT EXISTS条款区别对待？
解决方法
在某些情况下,您甚至可以编写(NOT)EXISTS(SELECT 1/0 …)并且结果相同 – 没有任何(除零)错误,这证明了那里的表达式甚至没有被评估.
关于LEFT JOIN / IS NULL反连接方法,一个修正：这相当于NOT EXISTS(SELECT …).
在这种情况下,NOT EXISTS vs LEFT JOIN / IS为NULL,您可能会得到不同的执行计划.例如在MysqL中,大多数在旧版本中(5.7之前),计划将非常相似但不完全相同.其他DBMS(sql Server,Oracle,Postgres,DB2)的优化器 – 据我所知 – 或多或少能够重写这两种方法并考虑两者的相同计划.仍然没有这样的保证,并且在进行优化时,最好从不同的等效重写检查计划,因为可能存在每个优化器不重写的情况(例如,复杂查询,具有许多连接和/或派生表/子查询内的子查询,其中来自多个表的条件,连接条件中使用的复合列)或优化器选项和计划受可用索引,设置等的不同影响.
另请注意,USING不能用于所有DBMS(例如sql Server).更常见的JOIN …… ON无处不在.
并且列需要在SELECT中使用表名/别名作为前缀,以避免在我们有连接时出现错误/歧义.
我通常也喜欢将连接列放入IS NULL检查中(尽管PK或任何非可空列都可以,当LEFT JOIN计划使用非聚集索引时,它可能对效率有用)：
SELECT a_table.a,a_table.b,a_table.c
FROM a_table
LEFT JOIN another_table
ON another_table.b = a_table.b
WHERE another_table.b IS NULL ;
使用NOT IN还有第三种反连接方法,但如果内部表的列可以为空,则它具有不同的语义(和结果！).通过将行排除为NULL可以使用它,使查询等同于前两个版本：
SELECT a,c
FROM a_table
WHERE a_table.b NOT IN
(SELECT another_table.b
FROM another_table
WHERE another_table.b IS NOT NULL
) ;
这通常也会在大多数DBMS中产生类似的计划.