[SQL][프로그래머스] 조건에 맞는 개발자 찾기 - & 연산자 + BITMASKING + NESTED SUBQUERY + DISTINCT + EXISTS MYSQL

https://school.programmers.co.kr/learn/courses/30/lessons/276034

프로그래머스

코드설명

& 연산자 + BITMASKING + NESTED SUBQUERY  + DISTINCT + EXISTS 를 활용합니다.

 

SQL에서도 비트마스킹을 사용할 수 있다는 점에 놀랐습니다.

FROM절안에 쿼리를 넣는 NESTED SUBQUERY, 즉 중첩서브쿼리를 활용하여서 정보를 가져옵니다.

 

이 문제 같은경우, 세 가지 풀이 방법으로 접근합니다.

첫번쨰는, INNER JOIN을 활용하여 'C#'과 'Python'의 기술코드를 가진 사람들을 JOIN한뒤, 두 개 중복 기술을 가진 사람은 DISTINCT로 중복제거합니다.

 

더 자세히보면, 

1. 서브쿼리를 통한 코드 추출: SKILLCODES 테이블에서 'C#' 또는 'Python'에 해당하는 CODE 값을 선택합니다. 이 서브쿼리는 SC1_SUBQUERY라는 이름으로 임시 테이블을 생성합니다.
2. INNER JOIN 사용: DEVELOPERS 테이블의 각 개발자 (D1)와 SC1_SUBQUERY의 각 코드 (CODE) 사이에 INNER JOIN을 사용합니다. 이때 조인 조건은 D1.SKILL_CODE & SC1_SUBQUERY.CODE = SC1_SUBQUERY.CODE입니다. 이 비트 연산은 DEVELOPERS 테이블의 SKILL_CODE에서 해당 CODE가 활성화(1)되어 있는지 확인합니다.
3. DISTINCT 사용: 개발자가 'C#'와 'Python' 두 기술을 모두 가지고 있다면, 이 조인 결과는 해당 개발자를 두 번 반환할 것입니다. 이 중복을 제거하기 위해 DISTINCT(D1.ID)를 사용합니다. 이렇게 하면 각 개발자 ID가 단 한 번만 나타납니다.
4. SELECT 절: 최종적으로 선택되는 필드는 개발자의 ID, EMAIL, FIRST_NAME, LAST_NAME 입니다. 여기서 AS 키워드를 사용하여 각 필드에 별칭을 지정합니다.
5. ORDER BY 절: 결과는 개발자 ID(D1.ID)에 따라 오름차순으로 정렬됩니다.

 

두번째는, 단순히 WHERE 조건절에 NESTED SUBQUERY를 사용하여 'Python' 혹은 'C#'의 기술을 가진 사람을 가져옵니다. 이렇게 작성할경우 이미 한번의 레코드에 WHERE 조건절이 필터링되므로 중복제거를 할필요가 없습니다.

 

세번째 방법은, EXISTS를 활용합니다. 먼저 EXISTS를 활용할 수 있는 이유는, 문제에서 CODE 테이블에 관한 정보를 요청하지 않기 때문에 가능합니다. 그렇기에 굳이 JOIN을 사용해서 CODE테이블의 데이터들을 JOIN안해도 되는것입니다. 하지만 단점도 존재합니다. NESTED SUBQUERY로 실행되기에 DEVELOPER 테이블의 개수만큼 매번 SQL이 실행됩니다. 

1. 외부 쿼리: DEVELOPERS 테이블에서 개발자의 ID, EMAIL, FIRST_NAME, LAST_NAME을 선택합니다. 이는 최종 결과로 보여질 정보입니다.
2. WHERE 절 내의 EXISTS 서브쿼리: 각 개발자(D1)에 대해, SKILLCODES 테이블에서 'Python' 또는 'C#' 이름을 가진 기술 코드를 찾습니다. 이 서브쿼리는 개발자의 SKILL_CODE 필드와 SKILLCODES 테이블의 CODE 필드간에 비트마스크 연산을 수행합니다. (D1.SKILL_CODE & SC1.CODE) = SC1.CODE 조건은 SKILL_CODE 비트마스크에 해당 CODE가 활성화(1)되어 있는지 확인합니다. 즉, 이 조건이 참이면, 해당 개발자는 'Python' 또는 'C#' 기술 중 하나 또는 둘 다를 가지고 있음을 의미합니다.
3. EXISTS: 이 함수는 서브쿼리가 적어도 하나의 행을 반환하면 참을 반환합니다. 즉, 만약 어떤 개발자가 'Python' 또는 'C#' 기술 코드와 일치하는 SKILL_CODE를 가지고 있다면, 해당 개발자는 최종 결과에 포함됩니다.
4. ORDER BY 절: 최종 결과는 개발자의 ID를 기준으로 오름차순으로 정렬됩니다.

 

코드 자체는 두번째 방법이 훨씬 간단하지만, 해당 코드는 SELECT문이 두번 실행되어, 불필요한 조회가 발생합니다.

하지만 첫번쨰 코드 또한 DISTINCT로 인해 중복제거 연산이 들어갑니다. 어떤 것이 더 빠른지는 직접 데이터셋에서 검토해봐야할 것 입니다.

 

코드

첫번쨰 방법.

SELECT 
    DISTINCT(D1.ID) AS ID, #DISTINCT를 활용하여 C#과 PYTHON이 둘다 있는 경우의 중복을 제거한다.
    D1.EMAIL AS EMAIL,
    D1.FIRST_NAME AS FIRST_NAME,
    D1.LAST_NAME AS LAST_NAME
FROM DEVELOPERS AS D1
INNER JOIN (SELECT 
                SC1.CODE AS CODE
            FROM SKILLCODES AS SC1 
            WHERE NAME IN ('C#', 'Python')) AS SC1_SUBQUERY
ON D1.SKILL_CODE & SC1_SUBQUERY.CODE = SC1_SUBQUERY.CODE
# ON D1.SKILL_CODE & SC1_SUBQUERY.CODE > 0 #참이라면, 이 경우가 성립하는 이유는 각 코드의 BIT에는 각 1개만 존재하기 때문이다.
ORDER BY D1.ID ASC;

 

두번쨰 방법.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME 
FROM DEVELOPERS
WHERE DEVELOPERS.SKILL_CODE & ( SELECT CODE FROM SKILLCODES WHERE NAME = 'Python' )
OR DEVELOPERS.SKILL_CODE & ( SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')
ORDER BY DEVELOPERS.ID ASC;

 

세번쨰 방법

SELECT D1.ID AS ID,
D1.EMAIL AS EMAIL,
D1.FIRST_NAME AS FIRST_NAME,
D1.LAST_NAME AS LAST_NAME
FROM DEVELOPERS AS D1
WHERE EXISTS (
    SELECT 1
    FROM SKILLCODES AS SC1
    WHERE SC1.NAME IN ('Python', 'C#') 
    AND (D1.SKILL_CODE & SC1.CODE) = SC1.CODE
)
ORDER BY ID ASC;

 

문제에서 놓졌던점은, INNER JOIN해서 여러 가지 테이블이 중복되기에 중복된 데이터가 나올 수 있습니다.

예를 들어, PYTHON이나 C# 의 기술이 모두 있는 개발자의 경우 두번 나옵니다.  

DISTINCT를 사용해서 한번만 출력되도록 합니다.

SELECT DISTINCT(D.ID), D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM SKILLCODES AS S
INNER JOIN DEVELOPERS AS D
ON S.CODE & D.SKILL_CODE > 0 
WHERE S.NAME IN ('PYTHON', 'C#')
ORDER BY D.ID ASC