以下のことが知りたいです。
これを自分の(例えばPHPの)アプリケーションで使おうと思っているのですが、データベースに対して複数のクエリを実行したくありません。
注:これを書いているのは、私がPHPのキューで常に遭遇する数多くの質問について、よく書かれたガイドにリンクできるようにしたいと思っているからです。
回答には以下のような内容が含まれています。
1.Part 1 - Joins and Unions 2.パート2 - サブクエリ 3.パート3 - トリックと効率的なコード 4.パート 4 - From 節のサブクエリ 5.Part 5 - Mixed Bag of John's Tricks
この回答では 1.パート1
mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)
ブランドの表は、車屋さんが販売する可能性のある車のさまざまなブランドを示しています。
mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)
モデルテーブルはさまざまなタイプの車をカバーしますが、実際の車のモデルではなく、さまざまな車のタイプを使用したほうがシンプルになります。
mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)
そして最後に、これらの他のすべてのテーブルを結びつけるためのテーブルです。IDフィールドは、実際に車を識別するために使用される固有のロット番号です。
mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)
これで、以下の様々なタイプの結合の例をカバーするのに十分なデータが得られると思います。
さて、本題に入りますが、ボスは持っているスポーツカーのIDを知りたがっています。
これは単純な2つのテーブルの結合です。モデルを特定するテーブルと、利用可能な在庫を持つテーブルがあります。ご覧のように、cars
テーブルのmodel
列のデータは、私たちが持っているcars
テーブルのmodels
列に関連しています。さて、modelsテーブルには、Sports
のIDが1
であることがわかっているので、結合を書いてみましょう。
select
ID,
model
from
cars
join models
on model=ID
さて、このクエリはいい感じですね。必要な情報を含む2つのテーブルを特定し、どのカラムで結合するかを正しく特定した結合を使用しています。
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
やばいですね。最初のクエリでエラーが発生しました。はい、それは梅です。このクエリは確かに正しいカラムを持っていますが、そのうちのいくつかは両方のテーブルに存在しているので、データベースは実際にどのカラムを意味しているのか、どこにあるのかについて混乱してしまいます。この問題を解決するには2つの方法があります。一つ目は簡単で、tableName.columnName
を使って、データベースに正確に意味を伝えることができます。
select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)
もう1つの方法は、おそらくより頻繁に使用され、テーブルエイリアシングと呼ばれます。この例のテーブルは短くていい名前ですが、KPI_DAILY_SALES_BY_DEPARTMENT
のようにタイプアウトするのはすぐに飽きてしまうでしょう。
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
さて、リクエストに戻りましょう。ご覧のように、必要な情報はありますが、求められていない情報もありますので、求められたスポーツカーだけを取得するために、ステートメントにwhere句を含める必要があります。私はテーブル名を何度も使うよりも、テーブルの別名を使う方法が好きなので、ここからはその方法を採用します。
明らかに、このクエリにはwhere句を追加する必要があります。スポーツカーは、ID=1
またはmodel='Sports'
のどちらかで特定できます。IDはインデックスされていて、主キーでもあるので(たまたま入力が少なくて済むので)、これをクエリに使いましょう。
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
ビンゴ!ボスは喜んでいます。もちろん、上司は自分が頼んだものに満足することはないので、情報を見て、「色も欲しい」と言います。 さて、クエリの大部分はすでに書かれていますが、3つ目のテーブルである色を使う必要があります。メインの情報テーブル「cars」には車の色のIDが格納されており、これが色のID列にリンクしています。そこで、元のテーブルと同様の方法で、3つ目のテーブルを結合することができます。
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
残念なことに、テーブルは正しく結合され、関連するカラムもリンクされましたが、先ほどリンクした新しいテーブルから実際の情報を引き出すことを忘れていました。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)
さて、これで一旦、ボスの話は終わりです。では、もう少し詳しく説明しましょう。ご覧のとおり、ステートメントのfrom
句はメインテーブルをリンクしています(私はルックアップテーブルやディメンションテーブルではなく、情報を含むテーブルを使うことが多いです。このクエリは、テーブルを入れ替えても同じように動作しますが、数ヶ月後にこのクエリを読み返したときには、あまり意味がありません。したがって、直感的にレイアウトしたり、きれいなインデントを使ったりして、すべてが可能な限り明確になるように、理解しやすいクエリを書いてみるのがよいでしょう。他の人に教える場合には、特にトラブルシューティングを行う場合には、彼らのクエリにこのような特徴を植え付けるようにしてください。
このような方法でテーブルをどんどんリンクしていくことは十分可能です。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
joinステートメントで複数のカラムを結合したいテーブルを入れるのを忘れていましたが、ここではその例を紹介します。もし
modelsテーブルがブランド固有のモデルを持っていて、そのために
brandというカラムを持っていて、
IDフィールドで
brands`テーブルにリンクしていたとすると、次のようになります。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1
上記のクエリは、結合されたテーブルをメインのcars
テーブルにリンクするだけでなく、既に結合されたテーブル間の結合も指定していることがわかります。これが行われなかった場合、結果はカルテシアン結合と呼ばれます。cartesian joinとは、情報がデータベースに結果をどのように制限するかを伝えていないため、クエリが条件に合う行をすべて返してしまうことです。
カルテシアン結合の例として、次のようなクエリを実行してみましょう。
select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)
やれやれ、醜いですね。しかし、データベースに関する限り、これは求められていたものと全く同じです。このクエリでは、cars
からID
を、models
からmodel
を求めています。しかし、テーブルを結合する方法を指定していなかったため、データベースは最初のテーブルのすべての行と2番目のテーブルのすべての行を照合しました。
さて、ボスが戻ってきて、また情報を欲しがっています。*同じリストが欲しいが、そこには4WDも入れてくれ」と。
しかし、これを達成するために、2つの異なる方法を検討する絶好の口実となります。次のように、where節に別の条件を追加することができます。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3
これは union
クエリがどのように動作するかを示す絶好の機会となります。
次のクエリはすべてのスポーツカーを返すことができます。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
そして、以下はすべての4WDを返します。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
そこで、これらの間に union all
句を追加すると、2番目のクエリの結果が1番目のクエリの結果に追加されます。
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)
ご覧のとおり、最初のクエリの結果が最初に返され、次に2番目のクエリの結果が返されます。
この例では、もちろん最初のクエリを単純に使用する方がはるかに簡単ですが、union
クエリは特定のケースでは非常に有効です。簡単には結合できないテーブルや、まったく関係のないテーブルから、特定の結果を返すのに最適な方法です。ただし、従うべきルールがいくつかあります。
union
と union all
の違いが気になるところですね。unionのクエリは重複を削除しますが、
union allでは削除しません。つまり、
unionを使うと
union all` に比べて若干のパフォーマンスの低下がありますが、それだけの価値がある結果になるかもしれません。
この点については、ここでいくつかの追加の注意事項を記しておく価値があるかもしれません。order by
を使うことができますが、エイリアスはもう使えません。上のクエリでは、order by a.ID
を追加するとエラーになります。両方のクエリで同じエイリアスが使用されているにもかかわらず、結果を見る限り、カラムはa.ID
ではなくID
と呼ばれています。order by
ステートメントは 1 つだけで、それは最後のステートメントでなければなりません。
次の例では、テーブルにいくつかの行を追加してみます。
brandsテーブルにHolden
を追加しました。
また、cars
にcolor
の値が12
の行を追加していますが、これはcolorsテーブルでは参照されていません。
よし、ボスがまた戻ってきて、要求を吠えています - *扱っている各ブランドの数と、その中の車の数を教えてくれ! - 典型的な例ですが、議論の面白い部分に入ったところで、ボスはもっと仕事をしたいと言っています。
そうですね、まず最初にやるべきことは、可能なブランドの完全なリストを手に入れることだと思います。select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
これを cars テーブルに結合すると、次のような結果になります。
select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)
これはもちろん問題で、私が追加した素敵なブランド Holden
が全く表示されていません。
これはjoinが2つのテーブルで一致する行を探すからです。carsにはHolden
という型のデータがないので、返されません。ここで、"outer "joinを使うことができます。これは、一方のテーブルの結果がもう一方のテーブルでマッチしているかどうかに関わらず、すべて*の結果を返します。
select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)
これができたら、集計関数を追加して数を取得し、上司の目を少しでも遠ざけることができます。
select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)
これで、ボスは逃げてしまいました。
さて、もう少し詳しく説明しますと、外部結合には「左」または「右」のタイプがあります。左か右かで、どのテーブルが完全に*含まれるかを定義します。左外部結合では、左のテーブルのすべての行が含まれます。一方、(お察しの通り)
右外部結合`では、右のテーブルのすべての結果が結果に含まれます。
一部のデータベースでは、両方のテーブルから(一致するかどうかにかかわらず)結果を返す「完全外部結合」が可能ですが、これはすべてのデータベースでサポートされているわけではありません。
さて、この時点で皆さんは、クエリの中で結合タイプをマージできるかどうか気になっていると思いますが、答えは「はい、絶対にできます」です。
select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)
では、なぜ期待した結果にならないのでしょうか?それは、車からブランドへの外部結合を選択したにもかかわらず、色への結合では指定されていなかったためです。 期待通りの結果を得るためのクエリは次のとおりです。
select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)
見ての通り、このクエリには2つの外部結合があり、期待通りの結果が得られています。 では、他のタイプの結合はどうでしょうか?交差点はどうでしょうか? すべてのデータベースが「インターセクション」をサポートしているわけではありませんが、ほとんどのデータベースでは、結合(または少なくとも構造化されたwhere文)によってインターセクションを作成することができます。 インターセクションは、上述したように「ユニオン」と似たタイプの結合ですが、ユニオンで結合された個々のクエリの間で同一の(つまり同一の)データ行のみを返すという違いがあります。すべての点で同一の行のみが返されます。 簡単な例を挙げると次のようになります。
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
通常の union
クエリはテーブルのすべての行を返し(最初のクエリは ID>2
以上のものを返し、2番目のクエリは ID<4
を持つものを返す)、結果的にフルセットとなりますが、intersect クエリは両方の条件を満たすため、id=3
にマッチする行のみを返します。
さて、もしあなたのデータベースが intersect
クエリをサポートしていない場合、上記は次のようなクエリで簡単に実現できます。
select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)
インターセクションクエリをサポートしていないデータベースを使用して、2つの異なるテーブルでインターセクションを実行する場合は、テーブルのすべての列で結合を作成する必要があります。
さて、ボスが再び登場しました - *I want a list of all of our cars with the brand and total of how one can have that brand is! これは、SQLの次の仕掛けであるサブクエリを使う絶好の機会です。サブクエリーとは、他のクエリーの中で実行されるクエリーのことです。副問い合わせには様々な使い方があります。 今回のリクエストでは、まず各車とブランドをリストアップするシンプルなクエリを作成してみましょう。
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
ここで、単にブランド別に並べられた車の数を取得したい場合は、もちろん次のように書くことができます。
select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand
+--------+-----------+
| brand | countCars |
+--------+-----------+
| BMW | 2 |
| Ford | 2 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+-----------+
つまり、元のクエリにcount関数を追加するだけでよいのですね。
select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 1 |
| 2 | Ford | 1 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 1 |
| 6 | BMW | 1 |
| 7 | Ford | 1 |
| 8 | Toyota | 1 |
| 9 | Toyota | 1 |
| 10 | BMW | 1 |
| 11 | Toyota | 1 |
+----+--------+-----------+
11 rows in set (0.00 sec)
残念ながら、それはできません。その理由は、車のID(a.ID列)を追加する際に、それをgroup byに追加しなければならないからです。そのため、count関数が動作する際には、IDごとに1つのIDしかマッチしません。
ここで、副問い合わせを使用することができます。実際に、今回必要な同じ結果を返す、2つの全く異なるタイプの副問い合わせを行うことができます。1つ目は、副問い合わせを単純にselect
句に入れる方法です。つまり、データの行を取得するたびに、副問い合わせが実行され、データの列を取得し、それをデータの行に挿入するのです。
select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 2 | Ford | 2 |
| 7 | Ford | 2 |
| 1 | Toyota | 5 |
| 5 | Toyota | 5 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 11 | Toyota | 5 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 6 | BMW | 2 |
| 10 | BMW | 2 |
+----+--------+-----------+
11 rows in set (0.00 sec)
そしてBam!これで完成です。お気づきのように、このサブクエリは、返すデータの行ごとに実行しなければなりません。この例でも、車のブランドは5種類しかありませんが、返すデータが11行あるので、サブクエリは11回実行されました。この場合、最も効率的なコードの書き方とは言えないでしょう。 別の方法として、副問い合わせを実行し、それがテーブルであると仮定してみましょう。
select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 5 |
| 2 | Ford | 2 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 5 |
| 6 | BMW | 2 |
| 7 | Ford | 2 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 10 | BMW | 2 |
| 11 | Toyota | 5 |
+----+--------+-----------+
11 rows in set (0.00 sec)
さて、同じ結果が得られました(順序が若干異なります - データベースは、今回選択した最初の列で順序付けられた結果を返したかったようです) - しかし、正しい数字は同じです。
では、この2つの副問い合わせの違いは何でしょうか?また、どのような場合にそれぞれの副問い合わせを使用するのでしょうか?まず、2つ目のクエリがどのように動作するかを確認しましょう。クエリのfrom
句で2つのテーブルを選択した後、クエリを書いて、実際にはテーブルであることをデータベースに伝えましたが、データベースは完全に満足しています。この方法を使用すると、いくつかの利点があります(いくつかの制限もあります)。最も重要な点は、この副問い合わせが1回しか実行されないことです。もしデータベースに大量のデータが含まれていたら、最初の方法よりも大幅に改善される可能性があります。しかし、これをテーブルとして使用しているため、データの行を追加して、実際にデータの行に結合できるようにしなければなりません。また、上記のクエリのように単純な結合を使用する場合は、データ行が十分にあることを確認しなければなりません。思い出してください。結合は、結合の両側に一致するデータがある行のみを取り出します。注意しないと、この副問い合わせで一致する行がなかった場合、carsテーブルから有効なデータが返されないという結果になりかねません。
さて、最初の副問い合わせを振り返ると、いくつかの制限があります。クエリのselect句で使用される副問い合わせは、多くの場合、sum、count、maxなどの集約関数のみを使用します。必ずしもそうしなければならないわけではありませんが、そのように書かれていることが多いです。
先に進む前に、副問い合わせを他にどのように使用できるかを簡単に見てみましょう。この例は少し作為的で、私たちのデータベースでは、以下のデータを取得するためのより良い方法がありますが、あくまでも例ですので、見てみましょう。
select
ID,
brand
from
brands
where
brand like '%o%'
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)
これは、名前に「o」を含むブランドIDとブランド名(2列目はブランドを示すためだけに追加されています)のリストを返します。 さて、このクエリの結果を次のようにwhere句で使用することができます。
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like '%o%'
)
+----+--------+
| ID | brand |
+----+--------+
| 2 | Ford |
| 7 | Ford |
| 1 | Toyota |
| 5 | Toyota |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
ご覧のとおり、サブクエリが3つのブランドIDを返していても、carsテーブルには2つのブランドのエントリしかありません。 この場合、さらに詳しく言うと、サブクエリは次のようなコードを書いたかのように動作しています。
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)
+----+--------+
| ID | brand |
+----+--------+
| 1 | Toyota |
| 2 | Ford |
| 5 | Toyota |
| 7 | Ford |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
ここでも、副問い合わせと手動入力によって、データベースから返される行の順序がどのように変更されたかがわかります。 副問い合わせについて説明している間に、副問い合わせでできることを他にも見てみましょう。
select
句に、いくつかをfrom
句に、さらにいくつかをwhere
句に入れることができますが、入れるたびにクエリが複雑になり、実行に時間がかかる可能性があることを覚えておいてください。
効率的なコードを書く必要がある場合、いくつかの方法でクエリを書き、どのクエリが結果を得るのに最適かを(タイミングを計ったり、説明プランを使ったりして)確認することは有益なことです。最初にうまくいった方法が必ずしも最良の方法とは限りません。今回は、これまでに出てきたヒントやコツについて、いくつか追加してみようと思います。
よくある質問に、「2つのテーブルから一致しない行を取得するにはどうすればいいですか」というものがあります。
select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)
そして、はいそれは動作します。
+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)
しかし、いくつかのデータベースでは、これは効率的ではありません。これについて質問しているStack Overflowの質問へのリンクと、もっと詳しく知りたければ優れた詳細記事があります。
簡単に言うと、オプティマイザーが効率的に処理できない場合は、以下のようなクエリを使って一致しない行を取得した方がはるかに良いでしょう。
select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null
+--------+
| brand |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)
ああ、これも懐かしい話ですが、昔からある You can't specify target table 'brands' for update in FROM clause.
MySQLでは、update...
のクエリを、同じテーブルのサブセレクトと一緒に実行することはできません。では、WHERE句に入れればいいのではないかと思われるかもしれません。しかし、たくさんの行の中で、「max()」の日付を持つ行だけを更新したい場合はどうでしょうか?これは正確には、where句ではできません。
update
brands
set
brand='Holden'
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can't specify target table 'brands'
for update in FROM clause
では、そんなことはできないのでしょうか?というと、そうではありません。驚くほど多くのユーザーが知らない巧妙な回避策がありますが、それには注意が必要なハッキングが含まれます。
サブクエリーを別のサブクエリーの中に入れることで、2つのクエリーの間に十分な隙間ができ、機能するようになります。これにより、クエリの実行中にテーブルに他の変更が加えられるのを防ぐことができます。
update
brands
set
brand='Holden'
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0