MYSQL の 1つのクエリで複数の結合を実行する
- MYSQL の 1つのクエリで複数の結合を実行する-クエリの構築
- MYSQL の 1つのクエリで複数の結合を実行する-自然結合を使用した 3 テーブル結合
-
MYSQL の 1つのクエリで複数の結合を実行する-
ONキーワードを使用した 3つのテーブルの結合 -
MYSQL の 1つのクエリで複数の結合を実行する-
WHEREブロック内の 3つのテーブルの結合 - MYSQL の 1つのクエリで複数の結合を実行する-外部結合の場合
MySQL の 1つのクエリに複数の結合を含める方法を考えたことはありますか？あなたは正しい場所に来ました。結合により、他のテーブルの情報に到達できることを忘れないでください。この情報は、冗長性を避けるために個別に含まれています。次の例を考えてみましょう。3つのテーブルを作成することから始めましょう。
client(client_id, client_name)は、client_idで識別され、client_nameという名前のクライアントを定義します。
CREATE TABLE client (
client_id INT PRIMARY KEY,
client_name VARCHAR(255)
);
product(product_id, product_name, unit_price, supplier_cost)は、product_idで識別され、unit_priceで販売されるproduct_nameという名前のストア内の商品を表します。サプライヤーから製品の 1 ユニットを購入するコストは、supplier_costによって与えられます。
CREATE TABLE product (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
unit_price INT,
supplier_cost INT
);
product_order(order_id, product_id, client_id, quantity)は、クライアントclient_idが購入した製品product_idを参照するorder_idによって識別される注文を表します。
CREATE TABLE product_order (
order_id INT PRIMARY KEY,
product_id INT NOT NULL,
client_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (product_id) REFERENCES product(product_id),
FOREIGN KEY (client_id) REFERENCES client(client_id)
);
ご覧のとおり、これはかなりミニマルですが、機能します。冗長な情報がないことに注意してください。製品の名前は product_order テーブルにありません。そうだとすれば、購入するたびにその商品の名前が繰り返されていたでしょう。
ここでの私たちの仕事は、クライアントごとに実現した利益を返すことです。ビジネスの観点からは、より複雑で有用なクエリを提案できますが、ここではマルチテーブル結合を示しています。データベースに次の値を入力して、クエリをテストできます。
INSERT INTO client VALUES (1, 'John');
INSERT INTO client VALUES (2, 'Mehdi');
INSERT INTO client VALUES (3, 'Ali');
INSERT INTO product VALUES (1, 'laptop', 500, 250);
INSERT INTO product VALUES (2, 'tablet', 600, 550);
INSERT INTO product_order VALUES (1, 1, 1, 3);
INSERT INTO product_order VALUES (2, 1, 1, 3);
INSERT INTO product_order VALUES (3, 2, 2, 6);
MYSQL の 1つのクエリで複数の結合を実行する-クエリの構築
注文に関連する利益は、次の方法で計算されます。
$$profit = quantity * (unit\_price - supplier\_cost)$$
ご覧のとおり、ターゲットクエリには、3つの値が必要です。数量は product_order にあり、単価とサプライヤーコストは product にあり、最後にクライアント名は client にあります。したがって、3つのテーブルの結合が必要です。各クエリの後にクエリ結果を提供します。
MYSQL の 1つのクエリで複数の結合を実行する-自然結合を使用した 3 テーブル結合
設計上、異なるテーブルの外部キーは、参照される主キーと同じ名前になります。自然結合を使用して、次の方法で 3つのテーブルをリンクできます。
SELECT client_name, SUM(quantity * (unit_price - supplier_cost)) AS profit
FROM product_order
NATURAL JOIN product
NATURAL JOIN client
GROUP BY client_id;
出力：
| client_name | profit |
| ----------- | ------ |
| John | 1500 |
| Mehdi | 300 |
MYSQL の 1つのクエリで複数の結合を実行する-ON キーワードを使用した 3つのテーブルの結合
私たちの目標を達成する別の可能性があります。次のように ON キーワードを使用できます。
SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order
JOIN product
ON product_order.product_id = product.product_id
JOIN client
ON product_order.client_id = client.client_id
GROUP BY client.client_id;
出力：
| client_name | profit |
| ----------- | ------ |
| John | 1500 |
| Mehdi | 300 |
MYSQL の 1つのクエリで複数の結合を実行する-WHERE ブロック内の 3つのテーブルの結合
最後に、結合が実行される条件を WHERE ブロック自体に組み込むことができます。
SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order
JOIN product
JOIN client
WHERE product_order.product_id = product.product_id
AND product_order.client_id = client.client_id
GROUP BY client.client_id;
出力：
| client_name | profit |
| ----------- | ------ |
| John | 1500 |
| Mehdi | 300 |
MYSQL の 1つのクエリで複数の結合を実行する-外部結合の場合
結合は、属性間の同等性の条件で実行されることを思い出してください。テーブルの特定の行にそのような同等性がない場合、結合された行は結果の結合に含まれません（内部結合がデフォルトです）。
これは問題になる可能性があります。
上記のクエリの場合、データベースに存在するが製品を購入したことのないクライアントは結果に表示されません。次の画像に示すように、product_order テーブルには関連する行がありません。
このようなケースは、一部のクライアントがアカウントを作成したがまだ何も購入していない Web アプリケーションが使用されている場合に発生する可能性があります。解決策は、以前の注文がないクライアントが NULL product_order 属性に関連付けられている LEFT OUTER JOIN を使用することです。
最後のクエリは次のとおりです。
SELECT client_name, SUM(IFNULL(quantity, 0) * (IFNULL(unit_price, 0) - IFNULL(supplier_cost, 0))) AS profit
FROM client
LEFT OUTER JOIN product_order
ON product_order.client_id = client.client_id
LEFT OUTER JOIN product
ON product.product_id = product_order.product_id
GROUP BY client.client_id;
出力：
| client_name | profit |
| ----------- | ------ |
| John | 1500 |
| Mehdi | 300 |
| Ali | 0 |
上記のように、現在のクライアントに注文がない場合、product_order テーブル属性は、数量を含めて NULL に設定されます。これは、product テーブル属性と同じです。これらのクライアントの利益値をゼロにしたい場合は、IFNULL 関数を使用して NULL 数量値をゼロに変換できます。
unit_price と supply_cost についても同じです。0 以外のデフォルト値を使用できます。IFNULL 関数の詳細については、ここををクリックしてください。
下の画像に、内側の結合と外側の結合を比較した図を示します。
