PostgreSQL LEFT JOIN: 잘못된 개수 결과 해결
PostgreSQL에서 LEFT JOIN을 사용하여 조직당 항목 수를 계산할 때 개수가 0보다 큰 조직만 반환되는 상황이 발생할 수 있습니다. 이는 조인된 테이블에 일치하는 항목이 있는지 여부에 관계없이 모든 조직을 포함해야 하는 LEFT JOIN의 예상 동작과 모순됩니다.
LEFT JOIN 쿼리 수정
일반적으로 문제는 필터링 조건을 잘못 배치할 때 발생합니다. 다음 수정된 쿼리는 올바른 접근 방식을 보여줍니다.
<code class="language-sql">SELECT o.name AS organisation_name, COUNT(e.id) AS total_used FROM organisations o LEFT JOIN exam_items e ON e.organisation_id = o.id AND e.item_template_id = #{sanitize(item_template_id)} AND e.used GROUP BY o.name ORDER BY o.name;</code>
중요한 수정은 e.used
조건을 WHERE
절(조인 이후 필터 역할을 함)에서 ON
절(조인 조건 역할을 함)로 이동하는 것입니다. ). 이렇게 하면 모든 조건을 만족하는 일치하는 행만 조인 자체에 포함되어 개수가 0인 조직이 의도치 않게 필터링되는 것을 방지할 수 있습니다.
최적화된 접근 방식: 사전 집계
특히 대규모 데이터세트를 처리할 때 성능을 향상하려면 다음 대체 쿼리를 고려하세요.
<code class="language-sql">SELECT o.id, o.name AS organisation_name, COALESCE(e.total_used, 0) AS total_used FROM organisations o LEFT JOIN ( SELECT organisation_id AS id, COUNT(*) AS total_used FROM exam_items WHERE item_template_id = #{sanitize(item_template_id)} AND used GROUP BY 1 ) AS e USING (id) ORDER BY o.name, o.id;</code>
이 방법은 먼저 하위 쿼리 내의 개수를 집계한 다음 조인을 수행합니다. 이 사전 집계는 특히 exam_items
행의 상당 부분이 개수에 포함될 때 효율성을 크게 향상시킬 수 있습니다. COALESCE
함수는 total_used
이 NULL(일치하는 항목이 없는 조직의 경우)인 경우를 처리하고 일관성을 위해 0으로 바꿉니다.
위 내용은 내 LEFT JOIN이 PostgreSQL에서 잘못된 개수를 반환하는 이유는 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!