PostgreSQL - IN vs ANY

Question

我都试过了:

1. smthng = ANY (select id from exmplTable)

2. smthng IN (select id from exmplTable)

和我的数据得到相同的结果。

这两个表达式有什么不同吗?

Answer 1

不，在这些变体中是相同的:

你可以看到-执行计划也是一样的:

<>之前Postgres =# explain select * from foo1 where id in (select id from foo2)┌──────────────────────────────────────────────────────────────────┐│查询计划│╞══════════════════════════════════════════════════════════════════╡│散列半连接(成本=3.25..21.99行=100宽度=4)││散列值:(1)Id = foo2.id)││-> Seq Scan on foo1 (cost=0.00..15.00 rows=1000 width=4)││->散列(cost=2.00..2.00 rows=100 width=4)││-> Seq Scan on foo2 (cost=0.00..2.00 rows=100 width=4)│└──────────────────────────────────────────────────────────────────┘(5行)Postgres =# explain select * from foo1 where id = any (select id from foo2);┌──────────────────────────────────────────────────────────────────┐│查询计划│╞══════════════════════════════════════════════════════════════════╡│散列半连接(成本=3.25..21.99行=100宽度=4)││散列值:(1)Id = foo2.id)││-> Seq Scan on foo1 (cost=0.00..15.00 rows=1000 width=4)││->散列(cost=2.00..2.00 rows=100 width=4)││-> Seq Scan on foo2 (cost=0.00..2.00 rows=100 width=4)│└──────────────────────────────────────────────────────────────────┘(5行)

Answer 2

这可能是一个边缘情况，但是:

select * from myTable where id IN ()

将产生:ERROR:语法错误"

,

select * from myTable where id = ANY('{}');

将返回一个空结果集

Answer 3

备注:已验证并正常工作

Create Table: user

CREATE TABLE user (
  id serial PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  skills VARCHAR[50]
);

插入数据

insert into user (username, skills) values ('user1', '{java, python}');
insert into user (username, skills) values ('user2', '{python}');
insert into user (username) values ('user3');

在上面的表中，当我们在列skills中搜索'python'时，它将返回2行。因为它匹配python的前两行。

SELECT * FROM user where  'python' = ANY (skills);

输出

 1 | user1 | {java, python}
 2 | user2 | {python}