👀 1. 들어가며

1.1. 상황

여러분들은 SQL에서 가장 많이 사용하는 연산은 무엇인가요? JOIN 연산이라고 답하는 분들이 많으실 것 같은데요.저 또한 실무에서 JOIN 연산을 많이 사용합니다. 내가 원하는 컬럼이나 테이블이 없는 경우엔 여러 테이블을 JOIN해서 내가 원하는 데이터를 만들어주는 작업이 필요하기 때문인데요! 많이 사용하는 연산인 만큼 JOIN연산 할 때 확인하면 좋은 5가지 체크리스트를 만들어봤습니다.

1.2. 예상독자

실무에서 SQL을 사용하는 분들이 읽으시면 도움이 되실 것 같습니다. (JOIN이 무엇인지 개념은 설명하고 있지 않아서, JOIN을 사용해보신 분들을 대상으로 하고 있습니다.) 5가지 체크리스트를 통해 JOIN연산 할 때 실수 없이 내가 원하는 결과를 확인하실 수 있을거예요.

🔎 2. SQL에서 JOIN 연산 할 때 확인 해야하는 5가지 체크리스트

(1) 결과를 예상해보자!

내가 JOIN을 통해 알고 싶은 정보와 결과, 어떤 테이블이 필요한지 작성해보세요. JOIN 연산을 하기 전에 예상되는 결과를 미리 파악하는 것이 중요합니다. 내가 어떤 결과를 원하는지 알아야 JOIN한 결과를 검증할 때 잘 동작하는지 판단할 수 있습니다. 많은 테이블을 여러 유형의 JOIN을 활용해서 작업하는 등 연산이 복잡해질 수록 꼭 필요합니다.

• 목적 : 2023년 7월에 가입해서 2023년 10월에 VIP등급이 된, 고객들이 가장 많이 주문한 상품명과 주문횟수를 확인하고 싶다.
• 결과 : 상품명, 주문횟수
• 필요한 테이블 :
  • 고객 정보 테이블 (customers) : 가입날짜
  • 주문 테이블(orders) : 주문횟수
  • 상품 테이블(products) : 상품명
  • 고객 등급 테이블(customer_grade) : 고객등급정보, 등급이 업데이트 된 날짜와시간

(2) 테이블 정보를 확인하자!

테이블 명세서를 확인하고, 테이블 정보가 작성된 문서를 찾아보는 등 테이블 정보를 확인하세요. 내가 사용하고자 하는 테이블이 어떻게 남고 있는지 모르고 JOIN하면 이슈가 발생해도 모르고 넘어가거나 빠르게 원인을 찾아낼 수 없습니다. 업무를 하면서 다른 동료들이 작성해 둔 쿼리나 테이블을 수정없이 그대로 사용하는 경우도 많으실거예요. 바쁘니깐, 업무가 많아 학습할 시간이 없어서 여러가지 이유가 있을 순 있지만 꼭 아래 3가지 작업은 직접 해보시면서 확인 후 사용하시길 바랍니다.

(2.1) 테이블 컬럼 목록 조회

INFORMATION_SCHEMA은 데이터베이스에 있는 스키마, 테이블, 컬럼, 인덱스 등 메타 정보를 포함하고 있습니다. INFORMATION_SCHEMA를 사용하면 데이터베이스에 있는 모든 테이블에 대한 정보를 조회할 수 있어, 데이터베이스의 구조를 이해하고, 특정 테이블을 이해하는데 큰 도움이 됩니다.

-- 스키마에 있는 테이블 목록 확인하기
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'

--- 테이블 정보(컬럼명, 데이터타입,key정보) 확인하기
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'

(2.2) 테이블 정보가 작성된 문서 조회

회사 내의 위키나 데이터카탈로그(기업 내 보유하고 있는 메타데이터를 수집하여 통합관리하는 서비스) 등에서 내가 사용하고자 하는 테이블을 검색해보세요. 데이터카탈로그에서는 테이블에 대한 기본 정보나 업데이트 시점 등을 파악할 수 있고, 위키 문서에서는 다른 동료들이 미리 테이블을 공부하면서 작성해 둔 테이블 사용 시 주의 사항 등이 기록되어 있을 수 있어 테이블을 파악하는데 큰 도움이 되실 겁니다!

(2.3) 직접 테이블 조회

가장 효과적인 것은 직접 테이블을 조회해보는 것입니다. 3개 정도 직접 조회해보면서 어떻게 남는지 확인해보세요. 이렇게 확인하여 알게 된 정보나 주의사항이 있다면 팀 내에 공유해봐도 좋고, 위키로 문서화해두는 것도 좋습니다. 다음에 이 테이블을 사용하는 사람들이 참고할 수 있도록 기여할 수 있는 방법이예요.

위와 같은 방법으로 조회해보니, 고객 등급 테이블(customer_grade)이 내가 원하는 테이블이 아니라는 것을 발견했습니다.

column_name	type	컬럼명	설명
customer_id	VARCHAR	고객id	고객의 고유 식별자
customer_grade	VARCHAR	고객등급	3등급으로 구성(Silvar, gold, vip)
last_updated_time	TIMESTAMP	등급이 업데이트 된 날짜와시간	최종 업데이트된 시점

제가 원하는 것은 "2023년 7월에 가입해서 2023년 10월에 VIP등급이 된, 고객들이 가장 많이 주문한 상품명과 주문횟수를 확인하고 싶다" 였잖아요. "2023년 10월에 VIP등급이 된 사람"을 보려고 했는데 고객 등급 테이블(customer_grade)은 고객 등급이 최종 업데이트된 시점 만 남고 있어요.

• 2023년 10월에는 vip등급이었지만 조회 시점인 2024년 2월 1일에 s등급이 된 고객 hj가 있다고 합시다.
• hj는 등급 테이블(customer_grade)에 고객등급 S등급, 업데이트 된 시점에 2024년 2월 1일이 남게 됩니다.
• 즉 이 테이블로는 2023년에 10월에 vip인 고객을 확인 할 수 없는 것이죠.

만약 이 사실을 모르고 JOIN했다면, 2023년 10월엔 vip였지만 그 이후 다른 등급으로 변경된 고객이 있다면 데이터 누락이 발생했을 거예요. 고객 등급 업데이트 로그가 남는 테이블이 있는지 찾아보니, 고객 등급 변경 로그 테이블(customer_grade_log)이 있네요.이 테이블을 사용하면 되겠어요!

column_name	type	컬럼명	설명
customer_id	VARCHAR	고객id	고객의 고유 식별자
customer_grade	VARCHAR	고객등급	3등급으로 구성(Silvar, gold, vip)
updated_time	TIMESTAMP	등급이 업데이트 된 날짜와시간	고객의 등급이 변경된 시점

(3) 어떤 JOIN을 사용할지 선택하자!

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN 등 다양한 JOIN 유형중에 어떤 JOIN을 사용할지 선택해야합니다. 테이블 위치도 함께 고민해봐야 합니다. 만약 LEFT JOIN을 한다고 할 때, 어떤 테이블을 왼쪽에 둬야할지 고민이 필요하다는 것인데요.

예를 들어, 고객 정보 테이블 (customers)과 주문 테이블(orders)을 JOIN해볼게요.

• (1) 고객 정보 테이블 (customers) LEFT JOIN 주문 테이블(orders)
  고객 정보가 왼쪽에 위치한다면, 모든 고객 정보를 가져오게 됩니다. 즉 주문을 한 번이라도 한 적이 없는 고객이라면 고객 정보는 존재하지면 주문 테이블은 NULL값이 나오겠죠.
• (2) 주문 테이블(orders) LEFT JOIN 고객 정보 테이블 (customers)
  주문 테이블이 왼쪽에 위치한다면, 모든 주문 정보를 가져오게 됩니다. 고객 정보 테이블에 탈퇴한 고객 정보는 삭제가 되고 주문 테이블에는 삭제가 안된다면 탈퇴한 고객이 주문한 경우, 고객 정보가 NULL로 남겠죠.

내가 목적하는 결과에 따라 어떤 JOIN을 사용할지 테이블 위치는 어떻게 둬야할지 달라지기 때문에 충분한 고민을 하고 선택해야 합니다.

(4) 어떤 열을 기준으로 JOIN할지 조건을 구상하자!

JOIN을 수행할 때 어떤 열을 기준으로 테이블을 JOIN할지 결정해야합니다.

(4.1) 고유 키와 외래키를 활용하자

중복된 값이 있는 열을 기준으로 JOIN하면 예상치 못한 대용량 데이터가 생성될 수 있으므로 고유 키(primary key) 나 외래키(foreign key)를 사용해보세요.

(4.2) 데이터 유형을 확인하자

JOIN에 사용되는 열은 동일한 데이터 유형이어야 합니다. 예를 들어, 문자형과 숫자, NULL값이 존재하는 컬럼과 NULL대신 O으로 채워진 컬럼으로 JOIN하면 원하는 결과를 얻을 수 없습니다.

(4.3) 시간이나 날짜 컬럼의 기준을 확인하자

JOIN할 테이블의 시간이나 날짜와 관련된 컬럼 기준이 동일한지 꼭 확인하세요. KST(Korea Standard Time)와 UTC(Universal Time Coordinated) 등 서로 다른 시간대로 적재될 수도 있기 때문입니다.

예를 들어, 고객 테이블은 KST기준으로 주문 테이블은 UTC기준으로 적재된다고 가정해볼게요.

• 고객이 KST 기준 으로 '2023-11-01 08:00:00'에 가입을 하고 주문을 한 경우, 이 데이터는 KST기준으로 적재되는 고객 정보 테이블에는 '2023-11-01 08:00:00'로 저장됩니다.
• 주문 테이블에는 UTC 시간으로 변환되어 '2024-10-31 23:00:00'로 저장되겠죠.
• 만약 '2023-11-01'을 기준으로 고객 테이블에 LEFT JOIN하여 주문 테이블을 붙인다면, 날짜 데이터가 일치하지 않아 해당 고객이 주문을 한 것으로 나타나지 않을 수 있습니다. 데이터 누락이 발생하는 것이죠.

따라서 JOIN 연산 전에 시간이나 날짜 컬럼의 기준을 확인하여 일치시키는 것이 중요합니다.

(5) 결과를 검증해보자

JOIN을 수행한 후 결과를 꼭 검증해야 합니다. 내가 예상한 결과가 나왔는지, 데이터 누락, 중복 등의 문제가 있는지 확인해야 합니다. 미처 파악하지 못한 컬럼 정보로 JOIN 조건이 잘못되어 의도하지 않은 결과가 나올 수 있기 때문입니다.

(5.1) COUNT, SUM, AVERAGE 등 확인

특정 컬럼의 수, 합계, 평균값 등을 확인하여 데이터의 일관성을 검증 해보세요! 예를 들어, 주문 테이블에서는 주문 금액의 합계를 계산하여 예상된 총 매출과 일치하는지 확인할 수 있습니다.

(5.2) NULL값 여부 확인

JOIN된 결과에서 JOIN되지 않은 NULL 값을 확인해보세요. IS NULL 또는 IS NOT NULL 조건을 사용하여 각 컬럼을 필터링하고, NULL 값을 가지는 컬럼이 내가 의도한 것이 맞는지 확인하고, 의도하지 않은 NULL값이 었다면 JOIN기준이 되는 열을 다시 점검해야합니다!

(5.3) 추가적인 데이터 비교

결과 데이터를 기존의 데이터 등과 비교하여 일치하는지 확인합니다. 예를 들어, JOIN된 결과와 기존의 데이터를 비교하여 일치하지 않는 행을 확인해볼 수 있어요~

🙌 3. 정리하며

실무에서 활용하기 위해 작성해본 JOIN연산 할 때 확인하면 좋은 5가지를 소개했습니다. 많이 사용하는 연산인 만큼 체크리스트를 만들어 실수하지 않고 꼼꼼하게 잘 챙기고자 했는데요. 이글을 보시는 독자분들도 실무에서 SQL JOIN연산을 사용하실 때, 5가지 체크리스트를 통해 실수 없이 내가 원하는 결과를 확인하시길 바랍니다! 본인 만의 체크리스트를 만드는 것도 추천드립니다!