LEFT JOIN クエリがカウント 0 の組織を返さないのはなぜですか?

PostgreSQL LEFT JOIN: ゼロカウント組織の問題への対処

PostgreSQL で LEFT JOIN を使用する場合の一般的な課題には、関連するレコードの数を取得することが含まれます。 特定の組織のカウントがゼロの場合、その組織は結果から除外される可能性があります。 この記事では、この問題について説明します。

次のクエリは、組織ごとに使用済みアイテムをカウントしようとします:

<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 句は、カウントがゼロの組織を除外します。 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 を使用することに注意してください。

これらのソリューションのいずれかを実装すると、カウントがゼロの組織も含め、すべての組織の使用済みアイテムの数を正確に取得できます。 データ サイズとパフォーマンス要件に最適なソリューションを選択してください。

以上がLEFT JOIN クエリがカウント 0 の組織を返さないのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。