[解決済み] SQL左結合とFROM行の複数のテーブルの比較？

質問

ほとんどのSQL方言は、次の両方のクエリを受け入れる。

SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x

SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x

外部結合が必要な場合、2番目の構文が必要なのは明らかです。しかし、内部結合を行う場合、なぜ最初の構文よりも2番目の構文の方が良いのでしょうか（またはその逆も同様）？

どのように解決するのですか？

旧来の構文では、テーブルをリストアップするだけで、そのテーブルを使用するために WHERE 節を使用して結合条件を指定することは、最近のほとんどのデータベースで非推奨となっています。

古い構文は、同じクエリでINNER結合とOUTER結合の両方を使用する場合に、曖昧になる可能性があります。

例を挙げましょう。

システムに3つのテーブルがあるとします。

Company
Department
Employee

各テーブルには多数の行があり、互いにリンクしています。複数の会社があり、それぞれの会社には複数の部門があり、それぞれの部門には複数の従業員がいます。

さて、では次のようにしたいと思います。

すべての会社をリストアップし、そのすべての部門とすべての従業員を含めます。まだ部門を持っていない会社もありますが、それらも含めてください。従業員がいる部門のみを検索し、常に全企業をリストアップするようにしてください。

つまり、こうするんですね。

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

最後の結合は、人がいる部門のみが必要という基準を満たすための内部結合であることに注意してください。

さて、ではどうなるのでしょう。問題は、データベースエンジン、クエリオプティマイザ、インデックス、テーブル統計に依存することです。説明しましょう。

クエリオプティマイザが、まず会社を取り、部署を見つけ、従業員と内部結合するのがよいと判断した場合、部署を持たない会社は出てきませんね。

その理由は WHERE 節は、どの 行 は最終的な結果で終わるのであって、行の個々の部分ではない。

そしてこの場合、左結合によりDepartment.ID列はNULLとなり、EmployeeへのINNER JOINの際、Employee行に対する制約を満たす方法がないため、表示されないことになります。

一方、クエリオプティマイザが部門-従業員結合に最初に取り組み、次に企業との左結合を行うと決定した場合、それらが表示されます。

つまり、古い構文はあいまいなのです。クエリヒントを使わない限り、何をしたいのかを指定する方法はありませんし、データベースによっては全くないものもあります。

新しい構文に入り、これで選択できるようになりました。

例えば、問題文にあるように、すべての会社が欲しい場合は、このように書きます。

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

ここでは、部署と従業員の結合を1つの結合として行い、その結果を会社と左結合することを指定しています。

さらに、名前に「X」が含まれる部署だけが欲しい場合を考えてみましょう。この場合も、古い形式の結合では、名前に X が含まれる部署がない場合、会社まで失うリスクがあります。

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

この追加句は結合に使用されますが、行全体に対するフィルタではありません。つまり、会社の情報を持つ行が表示されるかもしれませんが、その会社にはXを持つ部署がないため、その行の部署と従業員のカラムはすべてNULLになるかもしれません。これは、古い構文では難しいことです。

このため、他のベンダーの中でもMicrosoftは、SQL Server 2005以降、古い外部結合構文を非推奨とし、古い内部結合構文は非推奨としています。Microsoft SQL Server 2005または2008で動作しているデータベースと、古い形式の外部結合構文を使用して通信する唯一の方法は、そのデータベースを8.0互換モード（別名SQL Server 2000）に設定することです。

さらに、以前の方法では、クエリ・オプティマイザにたくさんのテーブルを投げて、たくさんのWHERE句を付けていましたが、それは「どうぞ、頑張ってください」と言っているようなものでした。新しい構文では、クエリオプティマイザは、どのようなパーツが一緒になっているかを把握するための作業を軽減することができるのです。

それでは、どうぞ。

LEFTとINNER JOINは、これからの時代の波です。