sql - 左连接返回重复行

Question

我刚刚开始学习 SQL，我真的很难理解为什么我的左连接返回重复的行。这是我正在使用的查询:

SELECT "id", "title"
FROM "posts"
LEFT JOIN "comments" "comment"
ON "comment"."post_id"="id" AND ("comment"."status" = 'hidden')

它返回4 行，但应该只返回 3 行。返回的行中有两行包含重复的(相同的值)。我可以通过在 "id" 上使用 DISTINCT 前缀来解决这个问题。

SELECT DISTINCT "id", "title"
FROM "posts"
LEFT JOIN "comments" "comment"
ON "comment"."post_id"="id" AND ("comment"."status" = 'hidden')

查询返回 3 行，我得到了想要的结果。但我仍然想知道为什么我首先会从第一个查询中得到重复的行？我正在尝试编写聚合查询，这似乎是我遇到的问题。

我正在使用 PostgreSQL。

更具体: (由我的 ORM 创建)

转移 DDL

CREATE TABLE shift (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    "gigId" uuid REFERENCES gig(id) ON DELETE CASCADE,
    "categoryId" uuid REFERENCES category(id),
    notes text,
    "createdAt" timestamp without time zone NOT NULL DEFAULT now(),
    "updatedAt" timestamp without time zone NOT NULL DEFAULT now(),
    "salaryFixed" numeric,
    "salaryHourly" numeric,
    "salaryCurrency" character varying(3) DEFAULT 'SEK'::character varying,
    "staffingMethod" character varying(255) NOT NULL DEFAULT 'auto'::character varying,
    "staffingIspublished" boolean NOT NULL DEFAULT false,
    "staffingActivateon" timestamp with time zone,
    "staffingTarget" integer NOT NULL DEFAULT 0
);

ShiftEmployee DDL

CREATE TABLE "shiftEmployee" (
    "employeeId" uuid REFERENCES employee(id) ON DELETE CASCADE,
    "shiftId" uuid REFERENCES shift(id) ON DELETE CASCADE,
    status character varying(255) NOT NULL,
    "updatedAt" timestamp without time zone NOT NULL DEFAULT now(),
    "salaryFixed" numeric,
    "salaryHourly" numeric,
    "salaryCurrency" character varying(3) DEFAULT 'SEK'::character varying,
    CONSTRAINT "PK_6acfd2e8f947cee5a62ebff08a5" PRIMARY KEY ("employeeId", "shiftId")
);

查询

SELECT "id", "staffingTarget" FROM "shift" LEFT JOIN "shiftEmployee" "se" ON "se"."shiftId"="id" AND ("se"."status" = 'confirmed');

结果

id                                      staffingTarget
68bb0892-9bce-4d08-b40e-757cb0889e87    3
12d88ff7-9144-469f-8de5-3e316c4b3bbd    6
73c65656-e028-4f97-b855-43b00f953c7b    5
68bb0892-9bce-4d08-b40e-757cb0889e88    3
e3279b37-2ba5-4f1d-b896-70085f2ba345    4
e3279b37-2ba5-4f1d-b896-70085f2ba346    5
e3279b37-2ba5-4f1d-b896-70085f2ba346    5
789bd2fb-3915-4cda-a3d7-2186cf5bb01a    3

Answer 1

如果一篇帖子有多个隐藏评论，您会多次看到该帖子，因为联接会为每个匹配项返回一行 - 这就是联接的本质。外部连接的行为没有什么不同。

如果您打算只列出带有隐藏评论的帖子，最好改用 EXISTS 查询:

SELECT p.id, p.title
FROM posts p
where exists (select *
              from comments c 
              where c.post_id = p.id 
                and c.status = 'hidden');