[DE 데브코스] 05.11 TIL - SQL JOIN과 NULL 처리

JOIN이란?

• 여러 테이블의 공통 필드를 가지고 병합하는 데 사용하는 명령어다.
• 여러 테이블로 분산되어 있던 정보를 통합하는 목적으로 사용한다.
• 조인 방식에 따라서 어떤 레코드들이 선택되는지, 어떤 필드들이 채워지는지 달라진다.
• 아래 도식화된 JOIN 종류를 보면 쉽게 이해할 수 있다.
• 일반적으로 외래키를 조인 속성으로 사용한다.
• 연결하려는 테이블의 조인 속성 이름은 달라도 되나, 데이터 타입(도메인)은 일치해야 한다.
• 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시한다.

JOIN의 종류 도식화

 

CROSS JOIN

• INNER, LEFT, RIGHT, FULL OUTER 말고도 CROSS JOIN이란 방식도 존재한다.
• 이는 카티션 프로덕트(Cartesian Product)라고도 하는데, 모든 경우를 고려하여 데이터를 조합하는 것이다.
• 만약 A라는 테이블의 레코드가 4개, B라는 테이블의 레코드가 3개 존재한다면, CROSS JOIN시 총 12개의 결과를 나타낼 것이다. INNER나 LEFT JOIN에 비하면 사용할 일이 사실상 없다.

 

BOOLEAN TYPE 처리와 NULL

• flag라는 필드에 True, False, NULL 값들로 레코드가 구성되어 있다고 가정하자.
• flag is True와 flag is not False는 동일한 표현일까? 그렇지 않다. 전자의 쿼리는 True만을 Count 할 테지만, 후자의 쿼리는 True뿐만이 아닌 NULL을 Count 할 것이기 때문에 결과 값이 달라진다.
• 수학적으로는 동일해 보이니까 두 쿼리를 혼용하여 사용할 수 있지만, SQL에서는 False와 NULL 값에 대한 처리가 다르므로 유의해야 한다.
• NULL 비교는 항상 is 혹은 is not으로 수행한다. = 혹은 != 혹은 <>로 수행하면 잘못된 결과가 나온다!
• NULL이 사칙 연산에 들어가면 모든 결과가 NULL이 된다. ZeroDivisionError를 피하기 위해 나눗셈할 때 NULL을 분모로 유도하기도 한다.
• NULLIF를 사용하여 0을 NULL로 변경하면 된다. ex) paidUsers/NULLIF(uniqueUsers, 0)

 

COALESCE

• NULL 값을 다른 값을 바꿔주는 함수다.
• COALESCE(exp1, exp2, exp3, ... )을 예시로 들면, exp1부터 인자를 하나씩 살펴서 NULL이 아닌 값이 나오면 그걸 리턴하는 것이다. 끝까지 갔는데도 모두 NULL이면, 최종적으로 NULL을 리턴한다.
• MySQL, PostgreSQL에 상관없이 공통으로 사용할 수 있다.

SELECT
    value,
    COALESCE(value, 0) -- value가 NULL이면 0을 리턴
FROM raw_data.count_test;

 

CASE WHEN 구문

• CASE WHEN 조건 THEN 반환 값 END을 기본 구조로 가진다.
• WHEN ~ THEN을 여러 번 사용해도 무방하다.

SELECT
    case when score_math >= 90 then 'A'
    when score_math < 90 then 'B'
    END as scores
FROM score_table;

 

 

공백 혹은 예약키워드를 필드 이름으로 사용

• 큰 따옴표로 이름을 둘러싸서 사용한다.

CREATE TABLE hongcana.test(
    stock_id int primary key,
    "company name" varchar(64)
);

 

SELECT 문의 실행 순서

• SELECT 쿼리문에서의 문법 순서와 실행 순서는 서로 다르다.
• 문법 순서는 SELECT -> FROM -> WHERE -> GROUP BY -> HAVIING -> ORDER BY로 작성하나,
• 실제 실행 순서는 FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY가 된다.
• 실행 순서를 달달 외울 필요는 없어 보이고, 쿼리 많이 찍어 보면 익숙해질 것이다.

 

나눗셈

• 다른 언어처럼 SQL에서 A / B로 나눗셈을 계산하면, 결과는 정수가 나온다.
• 그래서 퍼센트를 계산할 때 SQL은 ROUND()를 이용해서 처리를 해주고, 필요에 따라 소수점을 나타내야 할 때는 ::float로 숫자를 실수형으로 바꿔준다. 아래에 그 예시가 있다.

SELECT
    LEFT(created_at, 7) "month",
    COUNT(CASE WHEN nps.score <= 6 THEN 1 END) cnt_d,
    COUNT(CASE WHEN nps.score >= 9 THEN 1 END) cnt_p,
    COUNT(*) total_count,
    ROUND(cnt_p*100./NULLIF(COUNT(*), 0)::float,2) as percent_p,
    ROUND(cnt_d*100./NULLIF(COUNT(*), 0)::float,2) as percent_d,
    percent_p - percent_d as NPS
FROM raw_data.nps
GROUP BY 1
ORDER BY 1 ASC;

 

 

 

오늘 공부하며 어려웠던 내용

• JOIN이 3개 이상 겹칠 때는 머릿속으로 정리가 안 돼서 혼동이 되는 경우가 있다.. 내 머릿속 캐시 메모리가 부족한 탓인데, 이는 그냥 그림으로 JOIN을 도식화하면 해결할 순 있다. 코테 풀 때 펜으로 그려가면서 푸는 것처럼.