프로그래밍/SQL, Hive, SAS 관련 정보

[SQL 기초] SQL join

물박사의 저장공간 2024. 11. 2. 23:30

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

조인에 대해서 정리해보겠습니다. 사실 저는 습관적으로 left join을 애용하는데... 다른 조인을 사용해야할 경우를 대비해서 정리해두었다가 나중에 저도 참고하려고 합니다. 이번 포스팅은 아래와 같은 순서로 진행됩니다. 

 

1. 기본기능

2. 주의사항 : JOIN KEY의 중복

3. 실전문제

 

1. 기본 기능

1) INNER JOIN

두 테이블에서 일치하는 행만 결합하여 반환합니다. 조인 조건에 만족하는 행들만 결과로 반환합니다. 조인 조건이 일치하지 않으면 해당 행은 제외됩니다. (이미지적으로 교집합을 상상하시면 되겠습니다)

2) LEFT OUTER JOIN (또는 LEFT JOIN)

설명: 왼쪽 테이블의 모든 행을 반환하며, 오른쪽 테이블과의 조건이 일치하지 않는 경우 NULL로 채웁니다. 왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에서 일치하는 데이터가 없으면 NULL로 표시됩니다.

3) RIGHT OUTER JOIN (또는 RIGHT JOIN)

오른쪽 테이블의 모든 행을 반환하며, 왼쪽 테이블과의 조건이 일치하지 않는 경우 NULL로 채웁니다. 오른쪽 테이블의 모든 데이터를 반환하고, 왼쪽 테이블에서 일치하는 데이터가 없으면 NULL로 표시됩니다.

 

INNER JOIN은 두 테이블에 기준 필드의 값이 모두존재하는 record만 불러오고

OUTER JOIN은 기준 필드의 값이 어느 한쪽에만 있어도 record를 불러옵니다. 

 

4) FULL OUTER JOIN

두 테이블의 모든 행을 반환하며, 어느 한쪽에만 존재하는 행이 있을 경우 다른 쪽 테이블의 값은 NULL로 채웁니다. 양쪽 테이블 모두의 데이터를 결합하여 반환하며, 조건에 일치하지 않는 부분은 NULL로 표시됩니다. (요건 이미지적으로 합집합을 상상하시면 되겠습니다)

5) CROSS JOIN

두 테이블의 모든 행을 결합하여 모든 조합을 생성합니다. 조인 조건 없이 모든 행을 결합하므로, 결과 행 수는 두 테이블의 행 수를 곱한 만큼 많아집니다. 각 행이 서로 다른 테이블의 모든 행과 조합됩니다. (이건 약간 Kronecker Product - 외적의 일종-를 상상하시면 될 것 같습니다.)

 

참고) 기준 필드에 NULL이 존재하는 경우

INNER JOIN : 기준 필드가 NULL일 때는 불러오지 않는다.

LEFT/RIGHT JOIN : 기준 필드가 NULL인 경우도 불러온다. 

OUTER JOIN : 기준 필드가 NULL인 경우도 불러온다.

 

참고) ON 절의 다양한 적용

on에 항상 등호(=)만 조건으로 걸 수 있는 것은 아닙니다.

on a.score BETWEEN b.Min_score and b.Max_score

와 같은 식으로 걸어주는 것도 당연히 가능합니다. 

 

 

말로만 설명하면 이해가 쉽지 않으니 예시를 통해 살펴볼까요? 아래와 같이 테이블 2개가 있다고 해보겠습니다. 

 

1) employees 테이블

employee_id first_name department_id
1 John 10
2 Jane 20
3 Bob NULL

 

2) departments 테이블

department_id department_name
10 Sales
20 Marketing
30 HR

 

더보기 
-- 데이터베이스 생성 (필요한 경우)
-- employees 테이블 생성
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    department_id INT
);

-- employees 테이블에 데이터 삽입
INSERT INTO employees (employee_id, first_name, department_id) VALUES (1, 'John', 10);
INSERT INTO employees (employee_id, first_name, department_id) VALUES (2, 'Jane', 20);
INSERT INTO employees (employee_id, first_name, department_id) VALUES (3, 'Bob', NULL);

-- departments 테이블 생성
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- departments 테이블에 데이터 삽입
INSERT INTO departments (department_id, department_name) VALUES (10, 'Sales');
INSERT INTO departments (department_id, department_name) VALUES (20, 'Marketing');
INSERT INTO departments (department_id, department_name) VALUES (30, 'HR');

INNER JOIN 

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
employee_id first_name department_name
1 John Sales
2 Jane Marketing

 

LEFT JOIN 

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
employee_id first_name department_name
1 John Sales
2 Jane Marketing
3 Bob NULL

RIGHT JOIN 

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
employee_id first_name department_name
1 John Sales
2 Jane Marketing
NULL NULL HR

FULL OUTER JOIN 

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
employee_id first_name department_name
1 John Sales
2 Jane Marketing
3 Bob NULL
NULL NULL HR

 

만약 Full Join 시 NULL이 생기는 부분을 처리해주고 싶으시다면 NVL(Oracle), coalesce를 활용해주시면 됩니다. 

CROSS JOIN 

SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;
first_name department_name
John Sales
John Marketing
John HR
Jane Sales
Jane Marketing
Jane HR
Bob Sales
Bob Marketing
Bob HR

 

 

2. 주의사항 : JOIN KEY의 중복

사실 그런데 실무에서 JOIN을 사용하다보면 위에서 살펴본 기본적인 JOIN의 기능을 혼동해서 생기는 문제보다는 JOIN KEY로 사용하는 칼럼에 중복이 존재하여 JOIN 후 중복이 발생하는 문제가 빈번하게 발생합니다. 중복이 발생하는 예를 한 번 살펴볼까요? 이 중복은 말씀드린 바와 같이 JOIN KEY의 중복으로 발생하기에 INNER JOIN을 쓰든 LEFT (OUTER) JOIN을 쓰든 발생할 수 있습니다. 

 

TABLE_A가 아래와 같이 있고

 

ID NAME
1 Alice
2 Bob
3 Charlie
4 David

 

TABLE_B가 아래와 같이 있다고 해봅시다. 

 

ID VALUE
1 Value1
1 DuplicateValue1
2 Value2
5 Value3

 

더보기 
CREATE TABLE TABLE_A (
    ID NUMBER,
    NAME VARCHAR2(50)
);

INSERT INTO TABLE_A (ID, NAME) VALUES (1, 'Alice');
INSERT INTO TABLE_A (ID, NAME) VALUES (2, 'Bob');
INSERT INTO TABLE_A (ID, NAME) VALUES (3, 'Charlie');
INSERT INTO TABLE_A (ID, NAME) VALUES (4, 'David');

 

CREATE TABLE TABLE_B (
    ID NUMBER,
    VALUE VARCHAR2(50)
);

INSERT INTO TABLE_B (ID, VALUE) VALUES (1, 'Value1');
INSERT INTO TABLE_B (ID, VALUE) VALUES (2, 'Value2');
INSERT INTO TABLE_B (ID, VALUE) VALUES (5, 'Value3');
INSERT INTO TABLE_B (ID, VALUE) VALUES (1, 'DuplicateValue1');

 

 

그리고 ID를 조인키로 INNER JOIN을 해보면

SELECT
    A.ID AS A_ID,
    A.NAME AS A_NAME,
    B.VALUE AS B_VALUE
FROM TABLE_A A
INNER JOIN TABLE_B B ON A.ID = B.ID;

 

A_ID A_NAME B_VALUE
1 Alice Value1
1 Alice DuplicateValue1
2 Bob Value2

 

과 같은 결과를 얻을 수 있습니다. 

기본적으로는 원 TABLE 자체에 중복 자체가 없도록 하는 것이 좋지만 사용용도나 구조에 따라서 불가피하게 발생하는 경우도 많습니다. 그래서 JOIN을 사용하기 전 반드시 JOIN KEY의 중복 여부를 살펴주셔야 합니다. 만약 JOIN KEY에 중복이 존재한다면 크게 두 가지 옵션이 있을 수 있는데요

 

1) JOIN 전에 중복을 제거해준다. 

GROUP BY나 DISTINCT를 이용해서 JOIN전에 JOIN KEY 중복을 제거하고 JOIN을 실행하면 문제를 원천차단하는 방법일 수 있습니다. 다만 이런식으로 선제적으로 중복을 제거하는 과정에서 정보의 손실(여러개 중 첫째만 선택한다든가 sum, avg등으로 취합한다든가)이 있을 수 밖에 없습니다. 

 

2) 중복을 감수하고 JOIN을 감행한다. 

정보의 손실을 감수하고 싶지 않다면 이제 어쩔수 없이 중복을 감수하고 JOIN을 수행해야 합니다. 이 때는 중복이 존재하는 JOIN KEY가 JOIN되면서 어떠한 결과 TABLE이 생성될 지 머릿속에 잘 그려두면서 쿼리를 작성해야 합니다. 

 

3. 실전 문제

JOIN에 중복이 존재 :

INNER JOIN/LEFT JOIN의 구분 + 중복을 감수하고 JOIN을 감행

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

 

New Companies | HackerRank

Find total number of employees.

www.hackerrank.com

 

일단 이 문제에서 INNER JOIN을 선택할 지 LEFT JOIN을 선택할지는 어떻게 결정할까요?

  • JOIN하고자 하는 테이블 중 한 군데에만 존재하는 데이터가 있을 수 있는지
  • 만약 그렇다면 그 데이터가 누락되어도 괜찮은지 생각해봐야 합니다. 

 

만약 이 문제에서 전체 테이블을 inner join한다면 모든 테이블이 join key로 연결되는 데이터만 가져올 것입니다. employee중에서는 manager가 없거나 그 위의 상사들이 없는 경우는 없을 것 같습니다. 그런데 상사들은 부하직원을 데리고 있지 않을 수도 있지 않을까요? 그리고 지금 문제는 기업 전체의 각 직급별 인원수를 세라는 문제라서 이런 경우라도 누락시키면 안됩니다 : 그렇다면 INNER JOIN이 아닌 LEFT JOIN을 써야겠군요. 

 

그리고 만약 조인을 하게된다면 JOIN KEY가 각 단계에서 "부하직원-소속 상사 코드"가 될텐데.. 밑으로 내려갈 수록 상사의 코드명은 계속 중복이 발생할 겁니다. 예를들어서 A라는 매니저는 A1이라는 직원과 A2라는 직원을 데리고 있을 수 있으니 A1과 A2의 매니저명은 동일하게 A라고 찍히게 되겠죠. 

다만, 이 문제의 목적은 각 직급별 서로 다른 사람이 몇명씩인지 세면 되는 문제라 중복을 감수하고 JOIN 시킨 후 Distinct를 활용해서 원하는값을 뽑아내면 됩니다. 

 

ELECT 
    C.company_code,
    C.founder,
    COUNT(DISTINCT LM.lead_manager_code),
    COUNT(DISTINCT SM.senior_manager_code),
    COUNT(DISTINCT M.manager_code),
    COUNT(DISTINCT E.employee_code)
FROM Company C
	LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code
	LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code
    LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
	LEFT JOIN Employee E ON M.manager_code = E.manager_code
GROUP BY 
    C.company_code,
    C.founder
ORDER BY C.company_code

 

JOIN에 중복이 존재 :

미리 중복을 제거하고 JOIN

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true

 

Interviews | HackerRank

find total number of view, total number of unique views, total number of submissions and total number of accepted submissions.

www.hackerrank.com

 

이 문제는 집계테이블인 View_Stats Table을 살펴보면 challenge_id를 key로 다른 테이블과 연결되는데요. 그대로 JOIN 하게되면 중복이 발생하게 됩니다. 예시로 나와있는 것만 해도 중복을 곳곳에서 확인할 수 있습니다. 

 

 

따라서 집계 테이블은 먼저 Group By를 통해 중복을 제거해주고 JOIN하는 전략을 택할 수 있습니다. 지금은 total sum을구하는 것이기에 일단 중간 레벨에서 sum을 구한 후 그것들을 다시 sum해도 원하는 totalsum이 되는 상황입니다. 때에 따라서는 이러한 전략이 먹히지 않을 수도 있기 때문에 문제상황에 따라 유동적으로 대처해야합니다. 

## c Contests   contest_id, hacker_id, name
## o Colleges   college_id contest_id
## h Challenges   challenge_id, college_id
## v View_Stats   challenge_id, total_views, total_unique_views (duplicates!!)
## s Submission_Stats  challenge_id, total_submissions, total_accepted_submission (duplicates!!)
##
## contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views
## contest_id asc, exclude sum()=0
##
## strategy : contest_id > college_id > challenge_id (many) -- v/s group by merge

select ct.contest_id, ct.hacker_id, ct.name,
sum(ss.totsub_sum) as sub_sum,
sum(ss.totacc_sum) as acc_sum,
sum(vs.totv_sum) as v_sum,
sum(vs.totuqv_sum) as uqv_sum
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join (select challenge_id, sum(total_submissions) as totsub_sum,
           sum(total_accepted_submissions) as totacc_sum
            from Submission_Stats
            group by challenge_id) ss on cl.challenge_id = ss.challenge_id
left join (select challenge_id, sum(total_views) as totv_sum,
           sum(total_unique_views) as totuqv_sum
            from View_Stats
            group by challenge_id) vs on cl.challenge_id = vs.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having sum(ss.totsub_sum) + sum(ss.totacc_sum) + sum(vs.totv_sum) + sum(vs.totuqv_sum) > 0
order by ct.contest_id

 

참고) https://jinooh.tistory.com/58

 

HackerRank SQL - Interviews

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for

jinooh.tistory.com

 

유사한 문제로 다른 포스팅 문제를 살펴보는 것도 좋을 것 같습니다.

2025.03.10 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL문제풀이] 날짜 데이터 처리 Case