내 LEFT JOIN 쿼리에서 개수가 0인 조직을 반환하지 않는 이유는 무엇입니까?

PostgreSQL LEFT JOIN: 제로 카운트 조직 문제 해결

PostgreSQL에서 LEFT JOIN을 사용할 때 흔히 발생하는 문제는 관련 레코드 수를 검색하는 것입니다. 특정 조직에 대한 개수가 0인 경우 해당 조직은 결과에서 생략될 수 있습니다. 이 기사에서는 이 문제를 다룹니다.

다음 쿼리는 조직별로 사용된 항목을 계산하려고 시도합니다.

<code class="language-sql">SELECT o.name as organisation_name,
         coalesce(COUNT(exam_items.id)) as total_used
  FROM organisations o
  LEFT JOIN exam_items e on o.id = e.organisation_id
  WHERE e.item_template_id = #{sanitize(item_template_id)}
  and e.used = true
  GROUP BY o.name
  ORDER BY o.name</code>

이 쿼리의 결함은 WHERE 절에 있습니다. WHERE 절 필터 뒤에 배치된 조건은 LEFT JOIN에서 일치하는 레코드를 찾을 수 없을 때 INNER JOIN를 exam_items로 효과적으로 변환합니다.

개선된 버전은 다음과 같습니다.

해결책 1: JOIN 조건 수정

WHERE 절은 개수가 0인 조직을 필터링합니다. item_template_id 및 used 조건을 ON의 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>

이 수정된 쿼리를 통해 exam_items에 일치하는 항목이 없는 조직도 포함하여 모든 조직이 포함됩니다.

솔루션 2: 효율성을 위한 사전 집계

대규모 데이터 세트의 경우 사전 집계 exam_items를 수행하면 성능이 크게 향상될 수 있습니다.

<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 = TRUE
   GROUP BY 1
) e ON o.id = e.id
ORDER BY o.name, o.id;</code>

이 접근 방식은 먼저 각 조직의 개수를 집계한 다음 LEFT JOIN을 수행하여 보다 효율적인 쿼리를 수행합니다. COALESCE이 NULL인 경우를 처리하려면 total_used을 사용하세요.

이러한 솔루션 중 하나를 구현하면 개수가 0인 항목을 포함하여 모든 조직의 중고 항목 개수를 정확하게 검색할 수 있습니다. 데이터 크기와 성능 요구 사항에 가장 적합한 솔루션을 선택하세요.

위 내용은 내 LEFT JOIN 쿼리에서 개수가 0인 조직을 반환하지 않는 이유는 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!