[MY SQL] 대장균의 자식의 수 구하기(SELF JOIN, 서브쿼리, WITH절 사용방법)

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] :  그룹별 조건에 맞는 식당 목록 출력하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/299305

문제(일부): 대장균 개체의 ID와 자식의 수를 출력하는 쿼리 작성, 자식이 없다면 0으로 출력

---

1. SELF JOIN 사용하기

1) 동일한 테이블을 다른 별칭을 주어서 사용하는 SELF JOIN사용
2) JOIN 조건으로 e1테이블의 ID = e2테이블의 PARENT_ID, 해당 ID를 부모로 가지는 자식을 JOIN
3) LEFT를 사용하여 자식이 없어도 조회되도록 함

SELECT e1.ID, COUNT(e2.PARENT_ID) AS CHILD_COUNT
FROM ECOLI_DATA AS e1
    LEFT JOIN ECOLI_DATA AS e2
        ON e1.ID = e2.PARENT_ID
GROUP BY e1.ID
ORDER BY e1.ID

2. 서브쿼리 사용하기

JOIN조건을 서브쿼리의 WHERE절에 사용, 자식이 있는 ID를 COUNT하는 서브쿼리 생성

SELECT  ID,
         (SELECT COUNT(ID) 
         	FROM ECOLI_DATA 
            WHERE PARENT_ID = ED.ID) 
            AS CHILD_COUNT
 FROM    ECOLI_DATA ED
 ORDER BY ID ASC

3. WITH절 사용하기

1) PARENT_ID로 GROUP BY하여 PARENT_ID별 자식수를 COUNT하는 WITH절 생성
2) 메인쿼리에서 원본 테이블과 WITH절을 JOIN하여 ID별 자식수를 조회
3) COALESCE로 NULL이면 0을 반환하게 함
3) 조인 시 RIGHT로 자식이 없는 것도 조회되도록 함

WITH CTE AS(
    SELECT  PARENT_ID AS ID,
            COUNT(*) AS CHILD_COUNT
    FROM    ECOLI_DATA
    GROUP BY PARENT_ID
) # 자식의 수를 COUNT하는 WITH절 생성
SELECT  ED.ID,
        COALESCE(CTE.CHILD_COUNT,0) AS CHILD_COUNT
        # COALESCE는 NULL이 아닌 첫번쨰 값을 반환
        # CHILD_COUNT가 NULL이면 0을 반환
FROM    CTE
RIGHT JOIN ECOLI_DATA ED ON CTE.ID = ED.ID
        # RIGHT JOIN을 해야 자식이 없는 것도 포함
ORDER BY ED.ID ASC