SQL 이해해보기 #03 - 관계에 대해 살펴보기

이번엔 한번 RDB(Relational Database)가 존재하는 의미라고도 할 수 있는 "관계"에 대해서 생각해 보자. 개인적으로 SQL 의 관계에 대해서 잘 설명해 줄 수 있는 키워드 들은 JOIN, UNION, GROUP BY 라고 생각해서 세 개의 키워드의 개념이 왜 존재 하는 지에 대해서 설명해 볼까한다.

 

우리가 누군가와 썸을 탄다고 생각해 보자. 그렇게 되면 아마도 그 사람하고 뭔가 "연결(JOIN)"이 되는 것을 바라게 될 것이다. 무언가와 연결을 되려면 공통된 부분이 있어야 할테고, 우린 아마 상대방과 연결 될 수 있는 어떠한 연결 고리를 찾기 위해서 안테나를 세우게 될 것이다. 그래서 무언가가 혹시 닿게(ON) 된다면 썸이 연애로 발전하게 될 수도 있게 될 테이고 말이다. 두 개의 존재 사이에서 연결을 가져올 수 있는 무언가 동일한 것을 찾는 것처럼, SQL도 두 개의 테이블 간에서 같은 데이터들을 연결하는 기능을 제공하고, 이것을 JOIN 이라는 키워드를 사용해서 나타낸다. 실제 필드 에서는 여러 개의 테이블이 복합 해서 조인이 되거나, 특별한 관계를 뽑아내기 위해 자기 자신하고 조인을 하는 경우도 있긴 하지만 기본 적인 관계는 2개의 테이블에서 시작된다고 볼 수 있다.

<썸 타기>

사실 SQL 의 조인에는 INNER, LEFT, RIGHT, FULL, CROSS 등이 있다곤 하지만, 개인적으로는 한번에 다 머리 속에 억지로 넣어 놓고 헷갈리는 것보다는 INNER 와 LEFT 를 잘 이해하면 나머지는 실제 쿼리 들을 보거나 공부하면서 천천히 응용 방식을 익히면 된다고 생각한다. LEFT JOIN 을 보게 되면 SQL 에서 한번 쯤 꼭 만나야 할 NULL 의 의미도 생각해 볼 수 있게 되고 말이다. 관계형 이라는 말처럼 개별 문법을 익히는 게 중요한 게 아니라 어떤 유익한 점이 있어 그런 개념을 SQL 이 포함했을까를 고민하는 것이 맞다고 본다.

일단 JOIN 은 앞에서 봤던 엑셀 표 모양을 상상해 보면 2개의 테이블에서 각각 하나의 컬럼을 선택해서 두 개 안에 담긴 데이터 사이에서 동일한 값을 찾게 된다(기술적인 언어이기 때문에 유사어 같은 걸 찾진 않고 완전히 같은 데이터 값인 경우 매칭하게 된다). 매칭이 되면 테이블들의 하나의 줄 자체가 서로 매칭이 되는 거고, 만약에 한 쪽에 여러 개의 같은 데이터가 있다면 아래와 같이 그 수 만큼의 줄이 생기게 된다. 매칭이 안 되는 양 쪽 테이블의 모든 데이터 행 들은 썸 탈 때의 서로 상관없는 관심사처럼 INNER JOIN 에서는 아무 관심이 없다. 저 관계는 두 개의 테이블에 담긴 기준이 되는 같은 데이터가 어떤 것이냐에 따라서 의미를 가지게 된다. 

 

<Inner JOIN>

일단 JOIN의 시작은 앞의 시간에서 얘기했던 SELECT 문을 기본으로 진행 된다고 보면 된다. 앞에서 나왔던 SELECT 문을 생각해 보면 아래와 같은 형태이다. 낯설다면 방안의 서랍에서 물건을 찾아오던 그림을 떠올려 보자.

SELECT 대상 FROM 테이블 WHERE 조건

 

예를 들어 한 쪽 테이블에 회원 정보(회원 아이디, 성별, ...)가 있고, 다른 쪽에 구매 내역(회원 아이디, 구매 물품, ....)이 있다면 두 개를 조합하게 되면 특정 남자 회원들이 조합한 구매내역들을 뽑을 수가 있다. 예를 들면 이런 식의 쿼리가 될 것이다.

SELECT "구매상품들" FROM "회원정보 테이블" 
JOIN "구매내역 테이블" ON "회원정보의 구매아이디와 구매내역의 구매아이디 매칭" 
WHERE "회원정보의 성별이 남자인 경우"

또는 한쪽 테이블에 부서 정보(부서 코드, 부서 이름)가 있고, 다른 쪽에 직원 정보(직원 이름, 직원 아이디, 부서 코드)가 있다면 아래와 같이 사용할 수도 있다.

SELECT "부서 이름 및 사용자 이름" FROM "부서 테이블" 
JOIN "사원 테이블" ON "부서 테이블의 부서 아이디와 사원 테이블의 부서 아이디 매칭" 
WHERE "사원 이름이 홍길동"

결국 만능 키워드는 아니기 때문에 결국 사용하는 몇 가지 패턴이 있기는 하지만, 관계 자체만이 아니라 어떤 데이터가 저장되어 매칭 시키느냐에 따라서 사용하는 의미가 달라질 수 있다. 앞의 구매내역을 체크하는 곳에서도 SELECT 문에 "구매 상품들" 대신에 "구매 아이디" 넣는 다면 남자 회원들이 구매한 상품을 찾는게 아니라, 상품을 구매했던 남자 회원들을 찾는 쿼리로 의미가 바뀔 수도 있다. 결국 쿼리는 테이블 간의 관계 설계와 그 테이블이 현실과 매칭되는 의미에 따라서 비슷한 형태라도 의미가 달라질 수 있기 때문에, 해당 데이터의 해석에 따른 의미를 자연스럽게 가진다고 봐도 된다. 그런 관점이 없음 의미가 없기도 하고 말이다. 그래서 앞에서 얘기했던 SQL 을 이해하려면 데이터를 넣는 어플리케이션 단도 같이 이해해야 한다는 얘기도 그런 부분을 돕는 맥락이라고 이해를 해보자.

위의 회원정보와 구매 내역 쿼리를 설명했던 대목을 AI 한테 부탁해서 쿼리를 만들어 보면 아래와 같은 점잖은 SQL 쿼리가 나오게 된다. 테이블 뒤에 "AS 닉네임"으로 가정 하는건 쿼리의 가독성도 높여주지만, 두 개의 테이블에 같은 컬럼이 있을 경우에 구분할 수도 있고(디비에 따라서 임의로 해석해 주는 경우도 있지만, 어떤 디비는 명확히 구분해 호칭이 모호하다고 에러를 내기도 한다), 아까 얘기했듯이 자신과 자신을 JOIN 할수도 있기 때문에 임의의 네이밍을 붙이는게 여러모로 의미가 있다. 이것 저것 덕지덕지 추가됬긴 하지만, 여기서 위의 말로 표기한 의미가 대충 표현된 것이 이해된다면 성공했다고 볼 수 있다. 다른 분야도 마찬가지지만 이후 나머지는 자꾸 봐서 익숙해 지는 수밖에 없다.

SELECT 
    m.User_ID,
    m.Gender,
    p.Product_Name,
    p.Purchase_Date
FROM Member_Info AS m
JOIN Purchase_History AS p ON m.User_ID = p.User_ID
WHERE m.Gender = 'Male';

---

다음엔 LEFT JOIN을 함 이해해 보자. 개인적으로 첨에 봤을 때 INNER 조인, LEFT (OUTER) 조인 등의 차이가 많이 헷갈렸었는데, 지금 생각해보면 그게 왜 필요한지 잘 맘에 안 닿아서 그랬을 것도 같다. LEFT JOIN 은 위의 썸 얘기를 다시 빌려온다면 짝사랑에 가깝다. 썸은 서로 관심이 있어서 연결될 가능성이 높지만, 짝사랑은 상대의 마음을 전혀 모른다고 볼 수 있기 때문에 대부분 맹목적일 것이다. 상대의 모든 것을 신경쓰고 그 하나하나에 의미를 두게 될 것이기 때문이다. 

LEFT 조인은 비슷하게 내가 중요하다기 보다는 나의 왼쪽에 있는 상대의 모든 것을 기준으로 나를 판단하는 JOIN 형태라고 보면 된다. 뭐 현실적으로 보면 자신도 소중한 존재인건 맞지만 짝사랑에 빠지게 되면 상대의 모든 점에 대해서 내가 의미가 있는지를 살펴 보게 된다. 그리고 내가 그 상대가 가진 점에 대해서 특별한 연결 점을 찾지 못한다면 해당 부분의 나는 의미 없게 느껴지는 일도 생길 수 있다. 요 의미 없는 부분이 SQL 에서 얘기하는 NULL 이라고 봐도 된다(Null 은 순수 영어 뜻으로는 아무 힘이 없거나 비어있음을 얘기한다). JOIN 관계에서의 NULL은 상대에 비해서 아무 의미가 없다는 뜻으로 보게 되면 무방하지 않을까 싶다. 

 

<Left JOIN>

그래서 LEFT JOIN 의 의미도 위의 JOIN 과 비슷하게 여러가지 맥락을 가질 수 있다. 한 쪽이 회원 테이블이고 다른 한 쪽이 구매 테이블인 상태에서 LEFT JOIN을 한 후 구매 테이블의 회원 ID 가 NULL 인 줄(테이블 2개가 합쳐진 줄이긴 한다)을 조건으로 단다면 물품 구매를 안 한 회원을 찾아낼 수 있다. 일반적인 문장으로 표기한다면 아래와 같을 거다.

SELECT "회원정보 테이블의 구매회원들" FROM "회원정보 테이블" 
LEFT JOIN "구매내역 테이블" ON "회원정보의 구매아이디와 구매내역의 구매아이디 매칭" 
WHERE " 구매내역 테이블의 구매아이디가 NULL 인 경우"

위와 같이 진중한 쿼리로 만들어 본다면 아래와 같을 것이다.

SELECT 
    m.User_ID
FROM Member_Info AS m
LEFT JOIN Purchase_History AS p ON m.User_ID = p.User_ID
WHERE p.User_ID IS NULL;

또한 왼쪽 테이블의 데이터를 전체 표시하면서 해당 데이터에 해당되는 특정 조건이 없는 지를 전체 펼쳐서 체크해 보는 경우에도 사용할 수는 있을 것이다. 왼쪽 테이블의 데이터는 항상 모두 기본으로 가져가고 싶을 때 유용할 수도 있다.

---

다음에는 UNION을 생각해보자. UNION 은 정석으로 생각한다면 서로 형태가 같은 2개의 테이블을 하나의 테이블처럼 합쳐서 볼 수 있게 하는 거지만, 보안 취약점 공격인 SQL Injection에서 자주 일어나는 패턴까지 고려해 본다면, 테이블의 형태가 꼭 같을 필요는 없는 것 같고, 서로 다른 테이블 사이에서 같은 의미를 가진 데이터(SQL 인젝션 공격할 때는 수단과 방법을 안 가리기 때문에 이런 관계도 무시하긴 한다) 컬럼을 모아서 하나의 테이블을 만든다고 보면 될 것 같다. 아래와 그림과 같이 서로 다른 종류의 환경을 가진 사람들이지만 줄다리기라는 목표를 가지고 힘을 모아 동맹(UNION)을 결성해서 하나의 세력(SQL 에서는 TABLE)처럼 만들어 진다고 보면 어떨까 싶다.

<줄다리기>

테이블 뿐만 아니라 테이블에서 특정 컬럼을 모아서 SELECT 를 하면 하나의 결과 테이블이 된다는 확장 적인 관점에서 보면 UNION의 모습은 아래와 같다. 그림에서 만약 2개의 테이블이 완전히 동일하다면, 두 개의 테이블이 하나로 합쳐진 결과를 받을 수도 있다.

 

<UNION>

그럼 이런 형태의 관계는 어떤 의미가 있을 수 있을까? 예를 들어 테이블 형태가 같은 경우는 과거나 현재 테이블 또는 시간대 별 테이블을 합치는 경우가 있을 수 있다.

SELECT 동일 데이터 FROM "현재 테이블" 
UNION 
SELECT 동일 데이터 FROM "과거 테이블"

WHERE 조건은 각각의 SELECT 문이 있기 때문에 각각에 걸 수 도 있고, 두 개가 합쳐진 테이블에 통합해 걸 수도 있다. 두 개를 합칠 경우는 나중에 SELECT 추가 설명에서 얘기할 서브 쿼리 개념이 생기게 된다. 처음엔 좀 헷갈릴 수 있지만 쿼리의 반환 결과도 결국은 테이블 형태이기 때문에 그 가상의 테이블에 다시 쿼리를 날릴 수 있다고 보면 맘에 편하다. 이 무한 루프 이면서 재귀 호출 같은 부분이 SQL 문법의 강력함 중에 하나인 것 같고 말이다. 누구 표현으로 성능만 고려 안 한다면 어떻게든 원하는 데이터를 보여줄 수 있다고 하기도 한다.

SELECT 특정 조건 FRON
(
SELECT 동일 데이터 FROM "현재 테이블" 
UNION 
SELECT 동일 데이터 FROM "과거 테이블"
) AS "통합 테이블"
WHERE "통합 테이블 기준 조건"

위의 예제를 AI 한테 물어봐 만든 쿼리는 아래와 같다. 해석을 해보면 과거와 현재 거래 테이블에서 사용자와 금액을 모아서 10000만원 이상의 건만 찾는 것이다. 

SELECT 
    Total_List.User_ID,
    Total_List.Amount
FROM (
    SELECT User_ID, Amount AS Total_Amount
    FROM Sales_Present
    UNION ALL
    SELECT User_ID, Amount AS Total_Amount
    FROM Sales_Archive
) AS Total_List
WHERE Total_List.Total_Amount >= 100000

뭐 SQL 엔진이 어떻게 데이터를 찾아오려 할지(실행 계획이라고 한다)는 따져봐야 할 테지만, 현재 조건에서 저 거래 테이블에서 모든 데이터를 일단 다 가져오는 게 성능 적으로 맘에 걸린다면 아래와 같이 미리 각 테이블에서 100000만원 이상만 뽑아서 합치는 쿼리를 만들 수도 있을 것이다. 아마 요즘 SQL 엔진들은 프로그램 컴파일러 비슷하게 왠만하면 알아서 등가적으로 유리한 실행 계획을 세우긴 할 것 같긴 하다. 여튼 뭐 이런 부분은 비즈니스 설계가 데이터베이스 어떻게 녹아져 있고, 어떤 의미의 쿼리를 만들기를 원하냐에 따라서 달라질 것은 같다.

SELECT 
    Total_List.User_ID,
    Total_List.Total_Amount
FROM (
    SELECT User_ID, Amount AS Total_Amount
    FROM Sales_Present
    Amount >= 100000
    UNION ALL
    SELECT User_ID, Amount AS Total_Amount
    FROM Sales_Archive
    Amount >= 100000
) AS Total_List

또는 테이블 형태가 다른 경우도 있을 수 있을텐데, 괜히 설명만 길어지는 듯해서 그건 앞에서 본 예제에서 형태가 좀 다른 테이블에서 같은 형태의 컬럼을 SELECT 해서 나온 2개의 가상 테이블을 UNION해 합친다고 상상해 봄 될 것 같다. 이건 나중에 후반에 보안에서 SQL의 해석이 어떻게 필요한지에 대한 얘기를 하나 쓸거 같아서 그때 자세히 예제를 보자. 

현실에서 테이블 형태가 다른 경우라면 서로 형태가 다른 2개의 직원 테이블에서 공통된 정보(이름, 부서, 이메일)를 가져오는 경우가 있을 수 있을것 이다. 위의 예제에서도 Sales_Present와 Sales_Archive의 실제 컬럼이 완전히 같지 않더라도 User_ID, Amount 컬럼이 있거나 의미가 같은(컬럼 이름은 형식일 뿐이고 JOIN 에서 얘기했지만 데이터의 모양과 값 자체가 중요하다) 컬럼 들이 있다면 같은 이름으로 하나의 테이블을 만들 수 있을 것이다. 그래서 앞의 서로 다르지만 같이 힘을 모을 수 있는(데이터의 형태가 같은) 사람들이 모인 줄다리기 그림을 UNION 이라고 표현해봤다. 그리고 중복된 데이터를 제거하느냐의 차이를 가진 UNION ALL 이라는 키워드도 사족으로 있다.

---

마지막으로 테이블 간의 관계는 아니지만, 테이블 내의 데이터의 묶음 관계를 나타내기 때문에 현재의 글에서 같이 설명했음 하는 게 GROUP BY 이다. 가끔 여러 의미로 우리가 사회나 회사에서는 하나의 통계치로 나타날 뿐이라고 얘기하긴 하지만, GROUP BY 로 묶이는 순간에 그런 일들이 자동으로 이루어 지게 해준다. 

처음 GROUP BY 문을 만들게 되면 SELECT 문에 들어가는 컬럼이 왜 갑자기 이상하게 제한되게 되어 에러가 나는지에 대해서 헷갈리게 느낄 수 있는데, 이건 앞에서 얘기한 것 같이 GROUP BY 의 대상 이외에는 모두 통계적 익명성이 되기 때문 이라고 볼 수 있다.

<그룹>

위의 그림을 보면 우리가 해당 그림은 남자, 여자의 그룹 관점으로 보는 순간부터 남자 셋, 여자 셋이나, 남자들의 키의 평균, 여자들의 키의 평균, 남자들의 나이 합, 여자들의 나이 합 등으로 이루어질 수 밖에 없다. 그래서 GROUP BY 를 하는 순간 해당 GROUP BY 의 대상이 되는 컬럼은 그 대표성을 나타내는 남자, 여자 같은 키워드 역할을 하고, 나머지 컬럼 중에 통계적 의미를 가져올 수 있는 컬럼들은 SUM, COUNT, AVE 같은 수학 함수와 만나서 수치를 표기 하게 된다. 

왜 이렇게 답답하냐고 생각할수도 있지만 GROUP BY 가 집계를 기초로 한 통계 데이터를 쿼리를 통해서 쉽게 가져오기 하기 위한 것이기 때문에 어쩔수 없다고 생각하자. 그림으로 표시하면 아래와 같은 형태라고 볼 수 있다.

<GROUP BY>

말로 표시하자면 아래와 같을 것이다.

SELECT "분류를 원하는 데이터", COUNT, AVE, SUM 등(수치를 담은 컬럼이나 행 자체) 
FROM "집계 대상 테이블" 
GROUP BY "분류를 원하는 테이터가 들어가 있는 컬럼"

여기에서 "집계 대상 테이블" 에 추가로 제한을 넣고 싶다면 WHERE 문을 뒤에 넣음 된다. GROUP BY 를 통해 만들어진 테이블에 대해서 WHERE 조건을 넣으려면 조금 또 헷갈리는 키워드가 존재하는데, HAVING 이라는 키워드가 나온다. 위의 예제를 보면 컬럼 이름 이외에는 모두 통계적 수치이기 때문에 HAVING 에도 통계적 표현이 들어가야 해서 아마 좀 처음엔 의아할 것이다.

SELECT "분류를 원한는 데이터", COUNT, AVE, SUM 등(수치를 담은 컬럼이나 행 자체) 
FROM "집계 대상 테이블" 
GROUP BY "분류를 원한는 테이터가 들어가있는 컬럼" 
HAVING "같은 집계함수"

AI 를 통해 만든 아래의 쿼리를 보면 그룹에 등록한 20살 이상인 사람만 대상으로 스터디 그룹 별로 묶어서 평균 점수가 90점 인 스터디 그룹들만 "스터디 그룹 이름", "멤버 수", "평균 점수"를 표시해 준다. 

SELECT 
    Study_Group_Name,
    COUNT(*) AS Member_Count,
    AVG(Score) AS Average_Score
FROM Student_Registry
WHERE Age >= 20 
GROUP BY Study_Group_Name
HAVING AVG(Score) >= 90;

추가로 저 GROUP BY 도 2개 이상의 컬럼을 넣어서 특정 그룹안의 특정 타입 등으로 상세 분류로 집계할 수도 있겠지만(지역 별 스터디 그룹의 통계 숫자라든지) 시작은 위의 GROUP BY 개념에서 출발한다고 생각하고 찬찬히 따져보면 된다.

---

위에서 3개의 대표적인 관계를 나타내는 키워드 개념들을 설명했지만 사실 저건 어찌 보면 키워드 자체가 중요한 게 아니라 데이터를 해석하는 몇 가지 방식을 설명한 것이라고 보면 될 것 같다. SQL 은 그걸 쿼리로 테이블 안의 데이터를 지정해 쉽게 가져올 수 있게 구현한 개념이고 말이다. 여러 SQL 서버들은 그 개념들을 구현한 실체 들이다. 완전하게 같진 않지만 엑셀에서도 VLOOKUP 이라든가, GROUPBY, 피벗 테이블이라든지 vstack 같은 비슷하게 있다고 하고, 많은 데이터를 분석하는 툴에서도 마찬가지로 비슷한 관점의 기능이나 표현들이 있을 것이다. 

또한 위에서 얘기했듯이 기법은 데이터의 의미에 따라서 적용되는 패턴은 같지만 해석이 달라질 수 있다고 본다. 그래서 사실 적용하는 기법 이전에 중요한 게 데이터의 수집과 현실과 적절히 매칭된 설계라고 생각한다. 개인적으로는 현실의 모든 상황을 반영하는 완벽한 데이터가 세상에 존재할 수 있는지에 대해서는 조금 회의적이긴 한다. 이건 아날로그가 디지털로 완벽히 호환 가능하냐는 문제이기도 한 것 같고, 궁극적으로는 현실도 하나의 디지털 세상일 수 있을까 하는 문제하고도 연관되지 않을까 한다.

그래서 SQL 이 프로그램 세계에 기반한 엄청 기술적인 분야이기는 하지만, 반대로 보면 바깥의 데이터 수집 측면까지 고려했을 때 어느 정도 100% 완벽하진 않지만 확률이나 통계적으로 의미 있는 데이터의 저장과 해석을 추구한다는 시작점에서 지원되는 문법들을 봤으면 하는 생각도 있긴 하다. 또 나중에 이것과 다르게 데이터를 해석하려 하는 다른 툴과 기법들도 계속 관심을 가지면 좋겠다는 생각도 하고 말이다. AI 또한 그런 강력한 해석 툴 중에 하나라고 본다. 사실 뭐 이런 얘기에 대해서 근거에 기반에 얘기할 만큼 충분히 이 쪽 분야를 이해는 못한다고 생각하기 때문에 걍 개인적인 밤 시간의 지나가는 상념으로 봐줘도 무방할 듯은 하다.

- FIN -