甲骨文:'= ANY()' vs. 'IN()'

2021-12-06 00:00:00 sql syntax oracle

我刚刚在 ORACLE SQL 中偶然发现了一些我很好奇的东西(不确定它是否在其他人中).我在这里作为维基询问，因为很难尝试在谷歌中搜索符号......

I just stumbled upon something in ORACLE SQL (not sure if it's in others), that I am curious about. I am asking here as a wiki, since it's hard to try to search symbols in google...

我刚刚发现，当根据一组值检查一个值时，您可以这样做

I just found that when checking a value against a set of values you can do 

WHERE x = ANY (a, b, c)

相对于通常的

WHERE x IN (a, b, c)

所以我很好奇，这两种语法的原因是什么?一种是标准，另一种是一些古怪的 Oracle 语法吗?还是两者都是标准的?出于性能原因，是否有一种偏好，或者?

So I'm curious, what is the reasoning for these two syntaxes? Is one standard and one some oddball Oracle syntax? Or are they both standard? And is there a preference of one over the other for performance reasons, or ?

只是好奇有人能告诉我关于= ANY"的语法.加油！

Just curious what anyone can tell me about that '= ANY' syntax. CheerZ!

推荐答案

ANY(或其同义词SOME)是EXISTS 具有简单的相关性:

ANY (or its synonym SOME) is a syntax sugar for EXISTS with a simple correlation:

SELECT  *
FROM    mytable
WHERE   x <= ANY
        (
        SELECT  y
        FROM    othertable
        )

等同于:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   m.x <= o.y
        )

对于不可为空的字段上的相等条件，它变得类似于IN.

With the equality condition on a not-nullable field, it becomes similar to IN.

所有主流数据库，包括SQL Server、MySQL和PostgreSQL，都支持该关键字.

All major databases, including SQL Server, MySQL and PostgreSQL, support this keyword.

相关文章