代码之家  ›  专栏  ›  技术社区  ›  Rovanion

检查每个a是否至少存在一个b,其中每个b都有一个对a的外键引用

  •  0
  • Rovanion  · 技术社区  · 6 年前

    假设你有一张桌子 a 和一张桌子 b 其中每个b都有一个外键引用 的。如何编写SQL语句来回答每个a是否至少存在一个b的问题?

    create table users (
      id           bigserial  primary key,
      name         text
    );
    

    以及这些用户戴的帽子:

    create table hats (
      id           bigserial  primary key,
      user_id      bigserial  references users,
      description  text
    );
    

    您将如何编写一个查询来回答每个用户是否至少有一顶帽子,或者重新措辞:有没有没有没有帽子的用户?

    2 回复  |  直到 6 年前
        1
  •  0
  •   Lukasz Szozda    6 年前

    你可以用 LEFT JOIN 要查找不带帽子的用户:

    SELECT u.*
    FROM users u
    LEFT JOIN hats h
      ON u.id = h.user_id
    WHERE h.user_id IS NULL;
    
        2
  •  0
  •   Rovanion    6 年前

    你想要这样的结果吗 (user_id, has_hat) ? 或者只是有帽子的用户列表,或者没有帽子的用户列表?或者“does any user not have a hat?”的单个真/假结果

    对于各种不同的问题,最有效的答案是第一个问题:

    select exists(
      select 1
        from users u
        where not exists(
          select 1
            from hats h
            where h.user_id=u.id));
    

    这是因为它(a)可以规划为反连接,(b)在第一个匹配时停止。它还有一个额外的好处,就是字面意思是“一个用户的存在使得这个用户没有帽子”,所以它应该很容易为未来的读者理解。

    如果您想了解更多细节,下一个最好的选择是中间选项(带/不带帽子的用户),如下所示: --显示至少有一顶帽子的所有用户 选择* 哪里存在( 选择1 从帽子h

    -- shows all users with no hat
    select *
        from users u
        where not exists(
          select 1
            from hats h
            where h.user_id=u.id);  
    

    第一种方案的规划效率不高,因此通常应避免:

    -- shows all users, with a flag for whether they have a hat
    select u.id, exists(
          select 1
            from hats h
            where h.user_id=u.id) as has_hat
        from users u;