Navicatブログ

よくあるSQLクエリの間違い – パート3 2022年5月6日 by Robert Gravelle

外部結合とデカルト積

よくあるSQLクエリの間違いに関するこのシリーズでは、SQLクエリを構築する一見直感的な方法が誤った結果やパフォーマンスの低下につながるアンチパターンをもたらす可能性があることを調査してきました。先週、SQLの述語について話すためにこのシリーズをお休みしました。今回の記事では、それらの配置がクエリの実行にどのように悪い影響を与える可能性があるか、特に外部結合でどのように影響するかを学習します。

外部結合とは何ですか?

関連するテーブルとビューのリンクに使用される基本的な結合タイプには、内部、左、右、および外部の4つがあります。内部結合を使用すると、一方のテーブルの行のうち、もう一方のテーブルで一致しない行は返されません。外部結合では、一方または両方のテーブルで一致しない行が返されます。最後の3つの結合タイプは全て外部結合のインスタンスであり、次のようになります。

  • LEFT JOINは、左側のテーブルから一致しない行のみを返します。
  • RIGHT JOINは、右側のテーブルから一致しない行のみを返します。
  • FULL OUTER JOINは、両方のテーブルから一致しない行を返します。

外部結合がうまくいかない

データベース開発者は外部結合が必要とされない状況でもそれらを使用する傾向があります。さらに、外部結合クエリは、それを構築する方法や、クエリ内のどこに述語を配置するかによって、まったく異なる結果を生成する可能性があります。説明のために、例を見てみましょう。

2005年6月の初め以降に行った注文の総数とともに、全ての顧客(注文したかどうかに関係なく)のリストを取得したいと思います。そのためには、外部結合を使用して、次のようにcustomersテーブルとordersテーブルをリンクします。

SELECT C.customerName, count(O.customerNumber) AS 2005_orders
FROM customers AS C
LEFT OUTER JOIN orders AS O
  ON C.customerNumber = O.customerNumber
WHERE O.orderDate >= '2005-05-01'
GROUP BY C.customerName
ORDER BY 2005_orders DESC;

その結果には、デカルト積とも呼ばれる、1番目と2番目のテーブルの行の可能な全ての組み合わせが含まれている必要があります。残念ながら、Navicat Premium 16でクエリを実行すると、テーブルに122の一意の顧客が含まれていても、13行しか返されません。

customer_orders_bad (74K)

どこが間違っていたかを理解するために、1ステップずつクエリを再構築しましょう。列と外部結合のみから始めましょう。

outer_join_without_where_clause (121K)

今、私たちは全ての顧客を取得しています。ordersテーブルから取得しているため、注文を行っていないユーザーのcustomerNumbersはNULLです。

それでは、WHERE句の述語を適用しましょう。

outer_join_with_where_clause (90K)

突然、多くのお客様が無くなってしまいました!問題は、WHERE句の述語が外部結合を内部結合に変えたことです。

この問題を修正するためには、WHERE述語を結合条件に追加する必要があります。

outer_join_with_date (114K)

これで、元のクエリを調整でき、全ての顧客を取得できます。

customer_orders_good (89K)

このストーリーの教訓

フィルターをかけて行を除去する場所には常に注意してください。上記の例では、WHERE句が問題でした。複数の結合が発生するもっと複雑な例では、WHERE句ではなく、後続のテーブル演算子(別のテーブルへの結合など)で誤ったフィルタリングが発生する可能性があります。

シェア
ブログのアーカイブ