📊 쿼리 스터디(1) - IFNULL / JOIN 전 조건 / JOIN 후 조건

📊 테이블 : payment (수납/할인) / bill (청구)

 

수납/할인

청구

 

🧐문제 사항: 

청구 테이블과 수납 테이블이 있는데, 청구 테이블 항목에 대한 수납이 되었는지를 체크하고 싶다. 

 

다만 모두 납부 이외에도 부분적으로 찔끔찔끔 납부했을 가능성도 있는 상황

또한 수납 테이블은 고객이 수납한 금액도 쌓이지만 회사가 할인해주는 금액도 쌓인다.

(즉 청구금-수납금-할인금=0 이 되어야 완납이 된다.) 

 

💡솔루션:

SELECT bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money, SUM(IFNULL(pym.pym_amt, 0)),
SUM(IFNULL(sale.pym_amt, 0)),
(bill.charge_money - SUM(IFNULL(pym.pym_amt, 0)) - SUM(IFNULL(sale.pym_amt, 0) ) as left_money
FROM bill 
LEFT JOIN payment AS pym ON 
    bill.bill_item = pym.pym_item 
    AND bill.ban = pym.ban
    AND pym.code = 'PYM'
LEFT JOIN payment AS sale ON
    bill.bill_item = sale.pym_item 
    AND bill.ban = sale.ban
    AND sale.code = 'SALE'
GROUP BY bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money;

1. 조회

`SELECT bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money, SUM(IFNULL(pym.pym_amt, 0)), SUM(IFNULL(sale.pym_amt, 0)), (bill.charge_money - SUM(IFNULL(pym.pym_amt, 0)) - SUM(IFNULL(sale.pym_amt, 0)) as left_money`: 조회하려는 필드를 명시합니다. 

 🔴 GROUP BY

GROUP BY 항목으로 적지 않은 항목은 집계 함수와 함께가 아니면 단독으로 쓸 수 없다.

예를 들어, bill_date 같은 경우 GROUP BY로 묶여있지 않다. 따라서 SELECT 문 뒤에 단독으로 조회할 수 있는 컬럼으로 쓸 수 없다. 

집계함수 : max,count,sum,avg 등등...  

 🔴  IFNULL

`IFNULL`은 첫 번째 인자가 `NULL`인지 아닌지를 검사함. 만약 첫 번째 인자가 `NULL`이면, 두 번째 인자를 반환하고, 그렇지 않으면 첫 번째 인자를 반환.

SELECT IFNULL(name, 'No name') FROM users;

만약 `name` 값이 `NULL`이라면, 'No name'이라는 문자열을 대신 반환함

 🔴  SUM 집계함수 

(bill.charge_money - SUM(IFNULL(pym.pym_amt, 0)) - SUM(IFNULL(sale.pym_amt, 0)) as left_money

GROUP BY로 묶어 놓고, 각 각의 그룹에서 고객 납부 부분과  할인 부분의 SUM을 구해서 해당 청구 항목의 청구 금액에서 빼주는 방법이다. 

여기서 위에서 설명한 IFNULL을 사용한 이유는 다음과 같다. 

SELECT SUM(pym_amt) FROM payment WHERE code = 'PYM' 쿼리를 실행하면 결과는 NULL이다.  왜냐하면 NULL은 '알 수 없는 값'을 나타내기 때문에 어떤 숫자와 더하거나 빼도 결과는 '알 수 없는 값' 즉, NULL이 되기 때문!! 

IFNULL 함수를 사용하여 NULL 값을 '0'으로 대체하면, SELECT SUM(IFNULL(pym_amt, 0)) FROM payment WHERE code = 'PYM' 쿼리의 결과는 100이됨 ! 

마찬가지로, 우리는 지금 LEFT JOIN을 두 번이나 한 상태임으로!  무지막지하게 많은 null 값이 각 해당 데이터에 있을 가능성이 굉장히 높다!!

따라서, 나는 여기서 IFNULL을 사용해서 이 부분의 예외를 처리해줬다.

 

2. JOIN 전 조건 / JOIN 후 조건

`LEFT JOIN payment AS pym ON bill.bill_item = pym.pym_item AND bill.ban = pym.ban AND pym.code = 'PYM'`: 

`payment` 테이블을 `bill` 테이블에 결합

결합 조건은 `bill.bill_item`= `pym.pym_item`, `bill.ban`= `pym.ban` 그리고 `pym.code`가 'PYM'인 경우

만약, pym.code = 'PYM'` 이 부분이 ON 뒤에가 아니라, WHERE 로 이어지게 되면,

GPT썜에 따르면..  

---

`WHERE` 절과 `JOIN` 절에서의 조건은 다른 방식으로 작동합니다.

1. `JOIN` 절에서 조건을 사용하면, 그 조건은 두 테이블이 합쳐지기 전에 적용됩니다. 즉, 여기서 `pym.code = 'PYM'`은 `payment` 테이블에서 `code`가 'PYM'인 행만을 선택하여 `bill` 테이블과 결합합니다. 따라서 `pym`에 관련된 `SUM(IFNULL(pym.pym_amt, 0))` 계산은 `code`가 'PYM'인 행에만 적용됩니다.

2. `WHERE` 절에서 같은 조건을 사용하면, 그 조건은 모든 테이블이 합쳐진 후에 적용됩니다. 즉, `WHERE pym.code = 'PYM'` 조건은 `bill` 테이블과 `payment` 테이블이 결합된 후에 적용됩니다. 따라서 'SALE'에 해당하는 행들도 포함되어 `payment` 테이블에 'PYM'과 'SALE' 둘 다 있을 때는 결과가 달라집니다.

즉, 이 두 가지 조건은 다른 결과를 가져올 수 있습니다. 어떤 결과를 원하는지에 따라 적절한 위치에 조건을 배치해야 합니다.

---

 

예시 : 

 

SELECT bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money, SUM(IFNULL(pym.pym_amt, 0)), SUM(IFNULL(sale.pym_amt, 0)),
(bill.charge_money - SUM(IFNULL(pym.pym_amt, 0)) - SUM(IFNULL(sale.pym_amt, 0)) ) as left_money
FROM bill 
LEFT JOIN payment AS pym ON 
    bill.bill_item = pym.pym_item 
    AND bill.ban = pym.ban
    AND pym.code = 'PYM'
LEFT JOIN payment AS sale ON
    bill.bill_item = sale.pym_item 
    AND bill.ban = sale.ban
    AND sale.code = 'SALE'
GROUP BY bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money;

 

SELECT bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money, SUM(IFNULL(pym.pym_amt, 0)), SUM(IFNULL(sale.pym_amt, 0)),
(bill.charge_money - SUM(IFNULL(pym.pym_amt, 0)) - SUM(IFNULL(sale.pym_amt, 0)) ) as left_money
FROM bill 
LEFT JOIN payment AS pym ON 
    bill.bill_item = pym.pym_item 
    AND bill.ban = pym.ban
LEFT JOIN payment AS sale ON
    bill.bill_item = sale.pym_item 
    AND bill.ban = sale.ban
    AND sale.code = 'SALE'
WHERE pym.code = 'PYM'
GROUP BY bill.bill_sn, bill.ban, bill.bill_item, bill.charge_money;

따라서,  머리 속에서 테이블 파싱을 잘 하면서 조건문을 걸어줘야한다.

아니면, 이상한 결과가 나와서 긴 쿼리 다 쓰고 지워야할 경우가 생겨버린다!