检查多个表中是否存在一组值

Question

我想知道更好的方法来检查一组值是否是另一组值的子集。

出于某种原因，我无法使IN工作，所以我使用类似这种方法的方法：

-- check if `table1.id` is in other tables
SELECT (
-- check if all `table1.id` is in table2's `table1_id`
ARRAY(SELECT id FROM table1) <@ ARRAY(SELECT table1_id FROM table2)
AND
-- check if all `table1.id` is in table3's `table1_id`
ARRAY(SELECT id FROM table1) <@ ARRAY(SELECT table1_id FROM table3)
-- ...and so on
)

因此，例如，如果我table1上有这两行：

+----+
| id |
+----+
|  1 |
|  2 |
+----+

而table2上的这两行：

+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
+----+-----------+

而这一行在table3：

+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
+----+-----------+

结果将是false的，因为table3不包含1和2的line_id。

但是，如果table3如下所示：

+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
|  2 |         1 |
+----+-----------+

它会返回true

我的方法已经很好了吗？如果我正确使用IN，它会更快吗？还有其他一些我完全错过的方式吗？

Answer 1

您可以只使用内部连接并计算结果：

with table1_count as (
select count(*) as count
FROM table1
),
all_table_count as (
select count(*) as count
from (
select table1.id from table1
join table2 on table1.id = table2.table1_id
join table3 on table1.id = table3.table1_id
) sub
)
select table1_count.count = all_table_count.count as ids_everywhere
from all_table_count,table1_count
;
ids_everywhere
----------------
f
(1 row)

联接将比数组比较快得多。

Answer 2

使用exists

select t1.*
from Table1 t1
where exists (select 1 from table2 t2 where t2.table1_id = t1.id)
and   exists (select 1 from table3 t3 where t3.table1_id = t1.id)
and   exists (select 1 from table4 t4 where t4.table1_id = t1.id)

您还可以在案例语句中使用存在

select t1.id, 
case
when exists (select 1 from table2 t2 where t2.table1_id = t1.id)
and exists (select 1 from table3 t3 where t3.table1_id = t1.id)
and exists (select 1 from table4 t4 where t4.table1_id = t1.id)
then 1
else 0
end
from Table1 t1

或单独列出(编辑)：

select t1.id,
case
when exists (select 1 from table2 t2 where t2.table1_id = t1.id) 
then 1 else 0
end as in_tab2,
case 
when exists (select 1 from table3 t3 where t3.table1_id = t1.id) 
then 1 else 0 
end as in_tab3,
case 
when exists (select 1 from table4 t4 where t4.table1_id = t1.id) 
then 1 else 0 
end as in_tab4
from table1