优化sql查询：判断表B中的所有行，如果表C中的任何行引用表A中的同一行

发布于07月27日

我有3张表，A，B和C的 struct 如下

CREATE TABLE a (
    id SERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE b (
    id SERIAL NOT NULL PRIMARY KEY,
    a_id INT REFERENCES a(id) ON DELETE CASCADE
);

CREATE TABLE c (
    id SERIAL NOT NULL PRIMARY KEY,
    a_id INT REFERENCES a(id) ON DELETE CASCADE
);

关系是多对一的.我想要的是，对于表b中的每一行，我想要判断表c中的任何一行是否引用了表a中的同一行.现在，我已经有了查询

SELECT
    b.id,
    true
FROM
    b
WHERE EXISTS (
    SELECT 1
    FROM c
    WHERE b.a_id = c.a_id
)
UNION
SELECT
    b.id,
    false
FROM
    b
WHERE NOT EXISTS (
    SELECT 1
    FROM c
    WHERE b.a_id = c.a_id
)
ORDER BY id

虽然我不确定，但我认为这是在做双重工作，并且两次遍历表，我想知道如何优化它，使其只遍历表一次.

可以用一个简单的查询吗，或者我需要做一些复杂的事情吗？

推荐答案

这可以通过子查询、左连接和case来完成.

子查询将获得一个包含c.a_id个不同值的列表.

             SELECT DISTINCT a_id FROM c;

然后这样做

SELECT b.id, 
       CASE WHEN distinct_ids.a_id IS NULL THEN 'false'
            ELSE 'true' END has_c_row
  FROM b
  LEFT JOIN (
                SELECT DISTINCT a_id FROM c;
       ) distinct_ids ON b.a_id = distinct_ids.a_id

这种查询形状称为反连接或IS NULL ... LEFT JOIN.它检测第一个表中与第二个表中的行不匹配的行.

子查询为我们提供了表c中数据的视图，每个不同的a_id值最多有一行.如果没有子查询，我们可能会在结果查询中得到重复的行.

这消除了你的WHERE EXISTScorrelated subqueries；尽管PostgreSQL的查询规划器相当智能，但有时它在处理这样的子查询时做得很慢.

如果仍然太慢，请在a_id列上创建这些索引.

ALTER TABLE b ADD INDEX a_id (a_id);
ALTER TABLE c ADD INDEX a_id (a_id);

Sql相关问答推荐

在SQL Server中使用LEFT连接包含特定记录

SQL JOIN of 2 Table with 2 sum

SQL基于多个值 Select 单行

在数据库中搜索列

对多个条件的SQL进行排名

SQL按日期分组字段和如果日期匹配则求和

使用拆分器将已分组的不同值连接在一起

基于另一个(SAS、SQL)中的值更新列

同时插入和更新记录

在SQL中转换差异表的多列

将 jsonb 数组中的对象取消嵌套到单独的行中

删除对 JSON 数据的未解析引用的 SQL71502 警告

如何为 ActiveRecord 联接应用附加条件

Oracle SQL：通过将日期与另一个表行进行比较来 Select 值

获取所有用户的第一次和最后一次发货以及到达日期

达到特定值时，从0开始累加求和

Snowflake 中的对象是如何比较的？

SQL Server 查找存在于所有不同时期(或序列)中的条目

在 Microsoft SQL Server 中，如何只为特定值保留不同的行？

SQL：获取连接表的第一个项目

实用课程推荐