Guinguette Marais Poitevin

私は、連邦政府の若手開発者としての最初の SQL の仕事の 1 つを忘れられません。 あるクライアントのために、大臣室用の統計を作成するためのレポートを作成しなければなりませんでした。 レポートの数字が自分たちの予測と大きく異なっていたため、上司と私の間でミーティングが開かれました。 私はどこで何を間違えたのか分からなかったのですが、上司が「ロバート、君は直交積を作ってしまった」と言い出したのです。 上司はクライアントの前で私を叱りつけた後、ミーティングは終了し、私は自分のクエリを書き直しに行きました。 最終的にはクライアントが求めていたものが出来上がったのですが、上司はそれを許すことなく、その後の会議のたびにこの話を持ち出してきました。 “Robert, remember the time that you created a Cartesian Product… “と。 結局、半年ほどで別の部署に異動することになりました。 確かに上司は寛容な人ではありませんでしたが、このような失敗はキャリアに大きな影響を与えます。

Cartesian Product の生成方法

次のクエリは、フィルタリングなしで 2 つのテーブルからデータを抽出します。 WHERE 句を省略することは、テーブルのすべての行を見たいが、関連しない列を並べ替えたり隠したりしたい場合に役立ちます。

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM employees, shops; 

ここでの問題は、クエリが複数のテーブルから選択していることです。 明示的なテーブル結合がない場合、Cartesian Join (またはCross Join)と呼ばれる一種のデフォルト結合になります。 クロスジョインの名前は、1つ目のテーブルのすべての行を2つ目のテーブルのすべての行に結合することを意味しています。

結果を見ると、1つ目のテーブル（employees）のすべての行が、2つ目のテーブル（shop）のすべての行に返されていることがわかります。 shops テーブルには 3 つの行があるので、クエリは employees テーブルの各行を 3 つずつ生成します。 さらに多くの行やテーブルが含まれる場合、結果は指数関数的に増加します。 このようなクエリはシステム リソースに負担をかけ、結果として得られるデータ セットにはクエリ作成者が興味のあるものを選択するにはあまりにも多くの情報が含まれるため、Cartesian Join はほとんどの場合、誤って実行されます。 私の体験談にもあるように、顧客や上司から「なぜこんなに重複した行があるのか」と質問される前に、Cartesian Joinsの見分け方を知っておくとよいでしょう。

How Filtering Criteria Can Mask a Cartesian Product

WHERE 句を含む SELECT ステートメントは、すべての行が重複して表示されるわけではないので、Cartesian Product を簡単に隠すことができます。

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE shops.shop = 'Zurich';

ショップが結果セットに表示されていないので、出力が正確であることを簡単に受け入れることができます。 しかし、リストの最初の2人の従業員だけがチューリッヒで働いていることを簡単に確認することができます。 残りの 3 人は、フィルタリングの基準から外れているようです！

すべての列を出力すると、何が起こっているのかがより明確になります。 フィルタは実際には、チューリッヒの店舗のみを返していました。 しかし、適切なテーブル結合を行わないと、クエリは、チューリッヒ店にリンクしているかどうかにかかわらず、すべての従業員のレコードを生成します。 これは、ジョインなしでは従業員がどのショップにも関連付けられていないことを考えると納得できます。 したがって、employeeテーブルのshop_idフィールドは、shopのものとは何の関係もありません。 このクエリは、「ショップテーブルから名前が「Zurich」に一致するすべての行と、従業員テーブルからのすべての行を取得してください」と言っています。

shop_id	shop	id	shop_id_1	gender	name	alary
	Zurich			m	Jon Simpson
	チューリッヒ			f	Barbara Breitenmoser	(NULL)
	チューリッヒ			f td	Kirsten Ruegg
	チューリッヒ			m	Ralph Teller
	Zurich			m	Peter Jonson

似ていますね。 従業員テーブルからの結果を絞り込むと、そのテーブルからは基準に一致する行のみが生成され、他のテーブルからはすべての行が生成されます。 以下は、従業員を給与でフィルタリングするクエリです:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE employees.salary > 5500;

従業員テーブルの1行にマッチし、shopテーブルの各行に対して1回表示されます。

名前	性別	月給
Kirsten Ruegg	f	$5,600.00
Kirsten Ruegg	f	$5,600.00
Kirsten Ruegg	f	$5,600.00

繰り返しになりますが、すべての列を含めるとこの仮定が確認できます。

id	shop_id	gender	name	salary	shop_id_1	shop
		f td	Kirsten Ruegg			Zurich
		f	Kirsten Ruegg			ニューヨーク
		f	Kirsten Ruegg			ロンドン

さて、これらの結果はすぐには怪しいとは思えませんが、なぜでしょう。 なぜなら、基準や表示される列によっては、値が重複することは珍しいことではないからです。

このクエリは、依然として shops テーブルの各行に対して従業員を表示していますが、今回は shops テーブルの各行に対して従業員を表示するとともに、一致した 1 人の従業員に対して 3 行を表示しています。

今日見たように、デカルト積は有益な情報を提供しない傾向にあります。

Getting the Right Data with SQL Joins

” See All Articles by Columnist Rob Gravelle

の記事をすべて見る