[MYSQL] 대장균들의 자식의 수 구하기

문제

대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

 

내가 푼 쿼리

SELECT
    E1.ID,
    COUNT(COALESCE(E2.PARENT_ID)) AS CHILD_COUNT
FROM ECOLI_DATA E1
LEFT JOIN ECOLI_DATA E2
ON E2.PARENT_ID = E1.ID
GROUP BY 1;

동일한 테이블에 서로 다른 계층이 존재한다. 이런 경우, SELF JOIN을 활용하면 된다.

 

ECOLI_DATA 테이블은 아래와 같은 계층적 구조를 가지고 있다.

ID	PARENT_ID
1	NULL
2	1
3	1
4	2

각 행은 하나의 개체를 나타내며, PARENT_ID는 상위 개체의 ID를 참조한다. 즉, 1번은 최상위 개체이며, 2번과 3번은 1번의 자식이다.

 

SELF JOIN은 같은 테이블을 두 번 이상 조인하여 자신과 비교할 때 사용하는 방식이다. 이번 쿼리에서는 ECOLI_DATA 테이블을 E1, E2로 각각 참조하여 ID와 PARENT_ID를 비교했다.

1. JOIN 기준과 매칭 방식

• E1은 부모, E2는 자식 역할
• 따라서 E2.PARENT_ID = E1.ID 로 매칭해야 부모-자식 관계를 정확히 연결

2. LEFT JOIN을 사용하는 이유

• 자식이 없는 부모도 결과에 포함되도록 하기 위함
• LEFT JOIN은 왼쪽(E1)의 모든 레코드를 유지하고, 매칭되는 자식(E2)이 없어도 포함

3. COUNT 함수에서의 유의사항

• COUNT는 기본적으로 NULL 값을 제외
• 따라서 COUNT(E2.PARENT_ID) 또는 COUNT(COALESCE(E2.PARENT_ID))를 사용하면 자식이 있는 경우만 세어짐을 보장할 수 있음

 

SELF JOIN은 같은 테이블을 자기 자신과 조인해 관계를 분석하는 방식이며, LEFT JOIN을 활용하여 부모 기준으로 자식 개체를 연결하고, PARENT_ID와 ID를 매칭하여 계층 구조를 분석할 수 있다. COUNT 함수는 NULL을 제외하므로, 자식이 있을 때만 정확한 개수로 계산된다.