PostgreSQL の LEFT JOIN がカウント 0 の行を返さないのはなぜですか?

PostgreSQL の LEFT JOIN と欠落ゼロのカウント

PostgreSQL の LEFT JOIN は、右側のテーブルに一致する行がない場合でも、左側のテーブルからすべての行を返すように設計されています。 ただし、WHERE 句の条件が不適切に配置されていると、実質的に LEFT JOIN が INNER JOIN に変わり、カウントがゼロの行が省略される可能性があります。

この問題は、適切なテーブルに基づくフィルタリング条件が WHERE 句に配置されている場合によく発生します。 次の例を考えてみましょう:

<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 句は、特定の exam_items と item_template_id に対して used = true をフィルターします。これにより、結合の後がフィルタリングされ、これらの条件を満たす organisations 内に一致する行がない行を exam_items から削除します。

解決策: フィルタリングを JOIN 条件に再配置します

organisations に一致する行がない行も含め、exam_items のすべての行を保持するには、フィルター条件を WHERE 句から 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 = true
GROUP  BY o.name
ORDER  BY o.name</code>

現在、フィルタリングは結合中に行われます。基準を満たす exam_items 行のみが結合の対象となります。 一致する行がない organisations 行も引き続き含まれるため、total_used の値は 0 になります。

さらなる最適化: COUNT() と COALESCE

元のクエリでは COALESCE(COUNT(exam_items.id)) が使用されています。これは冗長です。 COUNT() は NULL を返しません。一致する行がない場合は 0 を返します。 したがって、COALESCE は不要であり、クエリ効率を向上させるために削除できます。

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