Mysql結合クエリー-JOIN-複数の関連テーブルからデータをクエリー
7146 ワード
格納する情報分類を異なるテーブルに格納し、異なるテーブル間をプライマリ・キーと外部キーで関連付けてリレーショナル・データベースを構成します.
たとえば、2つのテーブルを作成し、1つはベンダー情報を格納し、もう1つは製品情報を格納します.vendorsテーブルには、仕入先ごとに1行を占める仕入先情報が含まれており、各仕入先にはプライマリ・キー(primary key)と呼ばれる一意のIDがあります.Productsテーブルには製品情報のみが格納され、ベンダーID(vendorsテーブルプライマリ・キー)以外のベンダーの情報は格納されません.vendorsテーブルのプライマリ・キーはproductsテーブルの外部キーとも呼ばれ、vendorsテーブルとproductsテーブルを関連付け、ベンダーIDを使用してvendorsテーブルからベンダーの詳細を見つけることができます.
外部キーとは、別のテーブルのプライマリ・キー値を含むテーブルの列で、2つのテーブル間の関係を定義します.
このようなメリットは、
1、サプライヤーの情報は重複しないで、それによって時間と空間を浪費しない;
2、仕入先情報が変動した場合、vendors表の単一記録のみを修正することができ、関連表のデータは変更しない.
3、データが重複していないため、明らかにデータは一致しており、これによりデータの処理がより簡単になる.
内部連結クエリー
複数の関連テーブルからデータを問合せます.結合クエリーは、取得するカラムとテーブルがどのように関連付けられているかをリストするだけで簡単です.
たとえば、各ベンダーの名前と、提供される商品の価格と名前を検索します.
分析:
仕入先名はvendorsテーブルに格納され、商品名と価格はproductsテーブルに格納されます.この2つのテーブルの間にはvend_があります.id関連.
+-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | +-------------+----------------+------------+ 14 rows in set (0.06 sec)
カラムに二義性が生じる可能性がある場合は、本例のようにvendorsを完全に限定するカラム名を使用する必要がある.vend_name.
上記の例で用いた結合を等値結合または内結合と呼ぶ.このような結合に対して、INNER JOINを指定することができます...ON...キーワードは、結合のタイプを明確に指定します.
複数のテーブルの結合に制限はありません
連結クエリーは、テーブルの数に制限はありません.結合を作成するルールも同様です.まず、クエリーするテーブルをリストし、テーブル間の結合方法を定義します.
手順:
1.すべてのテーブルをリストするテーブル構造:DESC table_name
2、表の構造から、これらの表の中の同じ列が見えます.これらの表はこれらの列によって関連付けられています.
例:
取得order_num=20005の受注には、製品名、製品数量、製品価格、すでに仕入先名が含まれています.
考え方:
この例から分かるように、これらの情報はorderitems、products、vendorsの3つのテーブルにそれぞれ格納されている.
DESC orderitems、DESC products、DESC vendorsにより、ordersitem.prod_id=products.prod_id AND products.vend_id=vendors.vend_id.
+----------------+------------+----------+ | prod_name | prod_price | quantity | +----------------+------------+----------+ | .5 ton anvil | 5.99 | 10 | | 1 ton anvil | 9.99 | 3 | | TNT (5 sticks) | 10.00 | 5 | | Bird seed | 10.00 | 1 | +----------------+------------+----------+ 4 rows in set (0.03 sec)
連結クエリーは、サブクエリーの代わりに使用できる場合があります.
自己結合クエリー
自己結合クエリーは、通常、同じテーブルからデータを取得するときに使用されるサブクエリーの代わりに使用されます.これは、通常、自己結合クエリーがサブクエリーよりも高速であるためです.
例えば、DTNTRという商品を生産しているサプライヤーがどのような商品を生産しているのか知りたいです.これが典型的な自己結合応用である.productsテーブルからvend_をクエリーする必要があるためid,vend_によるid productsテーブルから仕入先の他の商品を検索します.クエリー全体では、他のテーブルは含まれておらず、productsテーブルを2回使用しています.
外部結合クエリー
前述の内連結とは、テーブルとテーブルの間に対応する関連行があることを意味します.ただし、別のテーブルでこのテーブルに対応するローが見つからない場合があります.例:
1、今まで注文したことがないお客様を含め、各お客様に対してどのくらい注文したかをカウントします.(注文していないお客様は、ordersテーブルに対応するcust_idが見つかりません).
2、すべての製品と注文数量をリストし、誰も注文したことがない製品を含む.
上記の例では、結合には、関連テーブルに関連行がない行が含まれています.このようなタイプの結合を外部リンクと呼ぶ.
例:
受注していない顧客を含む、クライアントとその受注番号を取得します.
コード:
+---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 6 rows in set (0.08 sec)
その結果、10002というお客様はまだ何も注文していません.
この例では、左外部ジョインクエリ、すなわちLEFT OUTER JOIN...ON....この結合は、「LEFTOUTER JOIN」の左のテーブルのすべての行を返し、右のテーブルに対応する行がなければNULLを返します.
RIGHT OUTER JOINもご利用いただけます...ON...,これにより、「RIGHT OUTER JOIN」の右側のテーブルのすべてのローが返され、左のテーブルに対応するローがなければNULLが返されます.
集約関数付き内部結合
集約関数は結合とともに使用できます.たとえば、すべての顧客とその受注数を取得します.
連結クエリーを使用して、すべての顧客の受注番号とその受注番号を問い合わせることができます.
+----------------+---------+-----------+ | cust_name | cust_id | order_num | +----------------+---------+-----------+ | Coyote Inc. | 10001 | 20005 | | Wascals | 10003 | 20006 | | Yosemite Place | 10004 | 20007 | | E Fudd | 10005 | 20008 | | Coyote Inc. | 10001 | 20009 | +----------------+---------+-----------+ 5 rows in set (0.10 sec)
結果に集約関数を使用する
+----------------+---------+-------------------------+ | cust_name | cust_id | COUNT(orders.order_num) | +----------------+---------+-------------------------+ | Coyote Inc. | 10001 | 2 | | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+-------------------------+ 4 rows in set (0.01 sec)
集約関数付き外部ジョイン上記の例を外部ジョインに変更します.
+----------------+---------+-------------------------+ | cust_name | cust_id | COUNT(orders.order_num) | +----------------+---------+-------------------------+ | Coyote Inc. | 10001 | 2 | | Mouse House | 10002 | 0 | | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+-------------------------+ 5 rows in set (0.00 sec)
注文していない10002のお客様も、検索されます.
たとえば、2つのテーブルを作成し、1つはベンダー情報を格納し、もう1つは製品情報を格納します.vendorsテーブルには、仕入先ごとに1行を占める仕入先情報が含まれており、各仕入先にはプライマリ・キー(primary key)と呼ばれる一意のIDがあります.Productsテーブルには製品情報のみが格納され、ベンダーID(vendorsテーブルプライマリ・キー)以外のベンダーの情報は格納されません.vendorsテーブルのプライマリ・キーはproductsテーブルの外部キーとも呼ばれ、vendorsテーブルとproductsテーブルを関連付け、ベンダーIDを使用してvendorsテーブルからベンダーの詳細を見つけることができます.
外部キーとは、別のテーブルのプライマリ・キー値を含むテーブルの列で、2つのテーブル間の関係を定義します.
このようなメリットは、
1、サプライヤーの情報は重複しないで、それによって時間と空間を浪費しない;
2、仕入先情報が変動した場合、vendors表の単一記録のみを修正することができ、関連表のデータは変更しない.
3、データが重複していないため、明らかにデータは一致しており、これによりデータの処理がより簡単になる.
内部連結クエリー
複数の関連テーブルからデータを問合せます.結合クエリーは、取得するカラムとテーブルがどのように関連付けられているかをリストするだけで簡単です.
たとえば、各ベンダーの名前と、提供される商品の価格と名前を検索します.
分析:
仕入先名はvendorsテーブルに格納され、商品名と価格はproductsテーブルに格納されます.この2つのテーブルの間にはvend_があります.id関連.
mysql> SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name;+-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | +-------------+----------------+------------+ 14 rows in set (0.06 sec)
カラムに二義性が生じる可能性がある場合は、本例のようにvendorsを完全に限定するカラム名を使用する必要がある.vend_name.
上記の例で用いた結合を等値結合または内結合と呼ぶ.このような結合に対して、INNER JOINを指定することができます...ON...キーワードは、結合のタイプを明確に指定します.
mysql> SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id ORDER BY vend_name;複数のテーブルの結合に制限はありません
連結クエリーは、テーブルの数に制限はありません.結合を作成するルールも同様です.まず、クエリーするテーブルをリストし、テーブル間の結合方法を定義します.
手順:
1.すべてのテーブルをリストするテーブル構造:DESC table_name
2、表の構造から、これらの表の中の同じ列が見えます.これらの表はこれらの列によって関連付けられています.
例:
取得order_num=20005の受注には、製品名、製品数量、製品価格、すでに仕入先名が含まれています.
考え方:
この例から分かるように、これらの情報はorderitems、products、vendorsの3つのテーブルにそれぞれ格納されている.
DESC orderitems、DESC products、DESC vendorsにより、ordersitem.prod_id=products.prod_id AND products.vend_id=vendors.vend_id.
mysql> SELECT prod_name,prod_price,quantity FROM orderitems,products,vendors WHERE orderitems.prod_id=products.prod_id AND products.vend_id=vendors.vend_id AND
order_num=20005;+----------------+------------+----------+ | prod_name | prod_price | quantity | +----------------+------------+----------+ | .5 ton anvil | 5.99 | 10 | | 1 ton anvil | 9.99 | 3 | | TNT (5 sticks) | 10.00 | 5 | | Bird seed | 10.00 | 1 | +----------------+------------+----------+ 4 rows in set (0.03 sec)
連結クエリーは、サブクエリーの代わりに使用できる場合があります.
自己結合クエリー
自己結合クエリーは、通常、同じテーブルからデータを取得するときに使用されるサブクエリーの代わりに使用されます.これは、通常、自己結合クエリーがサブクエリーよりも高速であるためです.
例えば、DTNTRという商品を生産しているサプライヤーがどのような商品を生産しているのか知りたいです.これが典型的な自己結合応用である.productsテーブルからvend_をクエリーする必要があるためid,vend_によるid productsテーブルから仕入先の他の商品を検索します.クエリー全体では、他のテーブルは含まれておらず、productsテーブルを2回使用しています.
mysql> SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR';外部結合クエリー
前述の内連結とは、テーブルとテーブルの間に対応する関連行があることを意味します.ただし、別のテーブルでこのテーブルに対応するローが見つからない場合があります.例:
1、今まで注文したことがないお客様を含め、各お客様に対してどのくらい注文したかをカウントします.(注文していないお客様は、ordersテーブルに対応するcust_idが見つかりません).
2、すべての製品と注文数量をリストし、誰も注文したことがない製品を含む.
上記の例では、結合には、関連テーブルに関連行がない行が含まれています.このようなタイプの結合を外部リンクと呼ぶ.
例:
受注していない顧客を含む、クライアントとその受注番号を取得します.
コード:
mysql> SELECT customers.cust_id,order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;+---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 6 rows in set (0.08 sec)
その結果、10002というお客様はまだ何も注文していません.
この例では、左外部ジョインクエリ、すなわちLEFT OUTER JOIN...ON....この結合は、「LEFTOUTER JOIN」の左のテーブルのすべての行を返し、右のテーブルに対応する行がなければNULLを返します.
RIGHT OUTER JOINもご利用いただけます...ON...,これにより、「RIGHT OUTER JOIN」の右側のテーブルのすべてのローが返され、左のテーブルに対応するローがなければNULLが返されます.
集約関数付き内部結合
集約関数は結合とともに使用できます.たとえば、すべての顧客とその受注数を取得します.
連結クエリーを使用して、すべての顧客の受注番号とその受注番号を問い合わせることができます.
mysql> SELECT customers.cust_name,customers.cust_id,orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY order_num;+----------------+---------+-----------+ | cust_name | cust_id | order_num | +----------------+---------+-----------+ | Coyote Inc. | 10001 | 20005 | | Wascals | 10003 | 20006 | | Yosemite Place | 10004 | 20007 | | E Fudd | 10005 | 20008 | | Coyote Inc. | 10001 | 20009 | +----------------+---------+-----------+ 5 rows in set (0.10 sec)
結果に集約関数を使用する
mysql> SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY cust_i
d;+----------------+---------+-------------------------+ | cust_name | cust_id | COUNT(orders.order_num) | +----------------+---------+-------------------------+ | Coyote Inc. | 10001 | 2 | | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+-------------------------+ 4 rows in set (0.01 sec)
集約関数付き外部ジョイン上記の例を外部ジョインに変更します.
mysql> SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY c
ust_id;+----------------+---------+-------------------------+ | cust_name | cust_id | COUNT(orders.order_num) | +----------------+---------+-------------------------+ | Coyote Inc. | 10001 | 2 | | Mouse House | 10002 | 0 | | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+-------------------------+ 5 rows in set (0.00 sec)
注文していない10002のお客様も、検索されます.