MYSQL에서 하나의 쿼리로 여러 조인 실행
- MYSQL에서 하나의 쿼리로 여러 조인 실행 - 쿼리 구성
- MYSQL에서 하나의 쿼리로 여러 조인 실행 - 자연 조인이 있는 3개 테이블 조인
-
MYSQL에서 하나의 쿼리로 여러 조인 실행 -
ON키워드로 3개 테이블 조인 -
MYSQL에서 하나의 쿼리에서 여러 조인 실행 -
WHERE블록 내에서 3개 테이블 조인 - MYSQL에서 하나의 쿼리로 여러 조인 실행 - 외부 조인 사례
MySQL에서 하나의 쿼리에 여러 조인을 포함하는 방법을 생각해 본 적이 있습니까? 당신은 바로 이곳에 왔습니다. 조인을 통해 다른 테이블의 정보에 접근할 수 있음을 기억하십시오. 이 정보는 중복을 피하기 위해 별도로 포함됩니다. 다음 예를 살펴보겠습니다. 먼저 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으로 명명된 상점의 제품을 나타냅니다. 공급자로부터 제품 한 단위를 구매하는 비용은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)는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에서 하나의 쿼리로 여러 조인 실행 - 쿼리 구성
주문과 관련된 이익은 다음과 같은 방식으로 계산됩니다.
$$profit = quantity * (unit\_price - supplier\_cost)$$
보시다시피 대상 쿼리에는 세 개의 값이 필요합니다. 수량은 product_order에서, 단가와 공급자 비용은 product에서, 마지막으로 클라이언트 이름은 client에서 찾을 수 있습니다. 따라서 3개의 테이블 조인이 필요합니다. 각 쿼리 후에 쿼리 결과를 제공합니다.
MYSQL에서 하나의 쿼리로 여러 조인 실행 - 자연 조인이 있는 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에서 하나의 쿼리로 여러 조인 실행 - 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에서 하나의 쿼리에서 여러 조인 실행 - 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에서 하나의 쿼리로 여러 조인 실행 - 외부 조인 사례
조인은 속성이 동일한 조건에서 수행된다는 점을 기억하십시오. 테이블의 특정 행에 이러한 동등성이 없으면 결합된 행은 결과 조인에 포함되지 않습니다(내부 조인이 기본 조인임).
이것은 문제가 될 수 있습니다.
위 쿼리의 경우 데이터베이스에 존재하지만 제품을 구매한 적이 없는 클라이언트는 결과에 나타나지 않습니다. 아래 이미지와 같이 product_order 테이블에는 연결된 라인이 없습니다.
이러한 경우는 일부 클라이언트가 계정을 생성했지만 아직 아무것도 구매하지 않은 웹 애플리케이션을 사용할 때 발생할 수 있습니다. 솔루션은 이전 주문이 없는 클라이언트가 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 테이블 속성은 product 테이블 속성과 동일한 수량을 포함하여 NULL로 설정됩니다. 해당 클라이언트에 대해 0의 이익 값을 원하면 IFNULL 기능을 사용하여 NULL 수량 값을 0으로 변환할 수 있습니다.
unit_price 및 supply_cost도 동일합니다. 0이 아닌 다른 기본값을 사용할 수 있습니다. IFNULL 기능에 대한 자세한 내용은 여기를 클릭하십시오.
내부 조인이 외부 조인과 어떻게 비교되는지에 대한 그림은 아래 이미지에 나와 있습니다.
