SQL:我们需要 ANY/SOME 和 ALL 关键字吗? [英] SQL: do we need ANY/SOME and ALL keywords?
问题描述
我使用 SQL(SQL Server、PostgreSQL)超过 10 年,但我从未在我的生产代码中使用过 ANY/SOME
和 ALL
关键字.我遇到的所有情况我都可以通过 IN
、MAX
、MIN
、EXISTS
逃脱,我认为它更具可读性.
I'm using SQL (SQL Server, PostgreSQL) over 10 years and still I'm never used ANY/SOME
and ALL
keywords in my production code. All situation I've encountered I could get away with IN
, MAX
, MIN
, EXISTS
, and I think it's more readable.
例如:
-- = ANY
select * from Users as U where U.ID = ANY(select P.User_ID from Payments as P);
-- IN
select * from Users as U where U.ID IN (select P.User_ID from Payments as P);
或
-- < ANY
select * from Users as U where U.Salary < ANY(select P.Amount from Payments as P);
-- EXISTS
select * from Users as U where EXISTS (select * from Payments as P where P.Amount > U.Salary);
使用ANY/SOME
和ALL
:
所以问题是:我错过了什么吗?是否存在 ANY/SOME
和 ALL
优于其他解决方案的情况?
So the question is: am I missing something? is there some situation where ANY/SOME
and ALL
shine over other solutions?
推荐答案
我发现 ANY 和 ALL 在您不只是测试相等或不相等时非常有用.考虑
I find ANY and ALL to be very useful when you're not just testing equality or inequality. Consider
'blah' LIKE ANY (ARRAY['%lah', '%fah', '%dah']);
ANY
、ALL
和它们的否定可以极大地简化代码,否则这些代码需要非平凡的子查询或 CTE,而且在我看来它们的使用率明显不足.
ANY
, ALL
and their negations can greatly simplify code that'd otherwise require non-trivial subqueries or CTEs, and they're significantly under-used in my view.
考虑到 ANY
可用于任何运算符.它对 LIKE
和 ~
非常方便,但也适用于 tsquery、数组成员测试、hstore 键测试等.
Consider that ANY
will work with any operator. It's very handy with LIKE
and ~
, but will work with tsquery, array membership tests, hstore key tests, and more.
'a => 1, e => 2'::hstore ? ANY (ARRAY['a', 'b', 'c', 'd'])
或:
'a => 1, b => 2'::hstore ? ALL (ARRAY['a', 'b'])
如果没有 ANY
或 ALL
,您可能必须将它们表示为子查询或 CTE,通过 VALUES
列表生成聚合一个结果.当然,如果你愿意,你可以这样做,但我会坚持使用 ANY
.
Without ANY
or ALL
you'd probably have to express those as a subquery or CTE over a VALUES
list with an aggregate to produce a single result. Sure, you can do that if you want, but I'll stick to ANY
.
这里有一个真正的警告:在较旧的 Pg 版本上,如果您正在编写 ANY( SELECT ... )
,则几乎可以肯定使用 存在(从...中选择 1 ...)
.如果您使用的版本中优化器会将 ANY (...)
转换为连接,那么您无需担心.如果有疑问,请检查 EXPLAIN
输出.
There's one real caveat here: On older Pg versions, if you're writing ANY( SELECT ... )
, you're almost certainly going to be better off in performance terms with EXISTS (SELECT 1 FROM ... WHERE ...)
. If you're on a version where the optimizer will turn ANY (...)
into a join then you don't need to worry. If in doubt, check EXPLAIN
output.
这篇关于SQL:我们需要 ANY/SOME 和 ALL 关键字吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!