Oracle:"= ANY()"与"IN()" [英] Oracle: '= ANY()' vs. 'IN ()'

问题描述

我只是偶然发现了ORACLE SQL中的某些内容(不确定是否存在于其他语言中)，对此我感到好奇.我要在这里作为Wiki，因为很难在Google中搜索符号...

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.

这篇关于Oracle:"= ANY()"与"IN()"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！