SQL (4) JOIN

이미지 출처

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)
c = conn.cursor()

• INNER JOIN: 내부조인 → 교집합
• LEFT/RIGHT JOIN: 부분집합
• OUTER JOIN: 외부조인 → 합집합

JOIN

앞에서 예제로 사용했던 테이블을 활용하겠습니다.

<대출내역, 도서명 테이블>

SELECT FROM 대출내역;
SELECT FROM 도서명;

만약 ID 101, 이름 문강태가 빌린 도서 ID를 보고 무슨 책인지 궁금하다면 <대출내역> 테이블에서는 도서명을 알 수가 없습니다.

또한, JOIN에서는 'KEY'가 있습니다. 'KEY'는 DB의 Index와 동의어입니다!

그렇다면 JOIN하여 확인해보겠습니다. 👍

JOIN의 기본 구문

SELECT 컬럼1, 컬럼2, 컬럼3 ... FROM A테이블 AS A
{INNER/LEFT/RIGHT/FULL OUTER} JOIN B테이블 AS B
ON A.결합컬럼 = B.결합컬럼
WHERE~

이 형태를 활용하여 각각 실습해 봅시다.

INNER JOIN

INNER JOIN을 통해 두 테이블의 교집합을 알 수 있습니다.

SELECT A.*, B.도서명
FROM 대출내역 AS A
INNER JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

query = '''
SELECT A.*, B.도서명
FROM 대출내역 AS A
INNER JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
	print(row)
    
>>>
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('103', '문상태', 'ccc', '공룡백과사전')

같은 도서 ID 컬럼을 활용하여 도서명을 불러왔습니다.

LEFT JOIN

LEFT JOIN은 왼쪽 A 테이블을 기준으로 오른쪽 테이블을 붙이는 것입니다.

SELECT A.*, B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

qeury = '''
SELECT A.*, B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''

for row in c.execute(qeury):
	print(row)

>>>
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('102', '고문영', 'fff', None)
('103', '문상태', 'ccc', '공룡백과사전')
('104', '강기둥', None, None)

기준이 된 대출내역 테이블은 변환없이 그대로 유지하고 있고, 오른쪽에 도서명 컬럼이 추가되었습니다. 정보가 없는 값은 자동으로 NULL이 되었습니다.

❗주의 사항

(주의) SQLite에서 실습할 때, 아래 소개할 RIGHT JOIN과 FULL OUTER JOIN은 아래와 같은 에러가 발생할 것입니다.

OperationalError: RIGHT and FULL OUTER JOINs are not currently supported

현재 SQLite에서는 RIGHT JOIN과 FULL OUTER JOIN를 지원하지 않습니다. 하지만 MYSQL 등 주요 RDBMS에서는 이런 형태의 JOIN도 지원되므로, 다음의 두 개념도 같이 숙지해 두시면 좋을 것 같습니다!

RIGHT JOIN

RIGHT JOIN은 B 테이블이 기준이 됩니다!

SELECT B.*, A.ID, A.이름
FROM 대출내역 AS A
RIGHT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

파이썬 sqlite3 모듈에서는 오류가 나니 RDBMS 에서 시행시켰을 때 결과를 이미지로 확인하겠습니다.

FULL OUTER JOIN

SELECT A.*, B.도서명
FROM 대출내역 AS A
FULL OUTER JOIN 도서명 AS B
ON A.도서ID = B.도서ID
OUTER BY 도서ID;

양쪽으로 값이 추가되었습니다.

중첩 질의(Nested Query)

SELECT C.이름, COUNT(*) 대출건수
FROM (
	SELECT A.*, B.도서명
    	FROM 대출내역 AS A
        LEFT JOIN 도서명 AS B
        ON A.도서ID = B.도서ID ) C
GROUP BY C.이름;

LEFT JOIN을 사용한 서브쿼리의 결과 C로부터 다시 쿼리를 수행하는 형태로 쿼리가 중첩되어 있습니다.

query = '''
SELECT C.이름 COUNT(*) 대출건수
FROM (
	SELECT A.*, B.도서명
    	FROM 대출내역 AS A
        LEFT JOIN 도서명 AS B
        ON A.도서ID = B.도서ID ) C
GROUP BY C.이름;
'''

for row in c.execute(query):
	print(row)

>>>
('강기둥', 1)
('고문영', 2)
('문강태', 1)
('문상태', 1)

쿼리의 조건절(1)-IFNULL

SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명
	FROM 대출내역 AS A
    	LEFT JOIN 도서명 AS B
        ON A.도서ID = B.도서ID;

IFNULL(값1, 값2)

이 함수는 값1 항목이 NULL인지 체크해서 NULL이면 값2를 대신, NULL이 아니면 값1을 그대로 RETURN해줍니다.

query = '''
SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명
	FROM 대출내역 AS A
    	LEFT JOIN 도서명 AS B
        ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
	print(row)
>>>
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('102', '고문영', 'fff', '도서명미상')
('103', '문상태', 'ccc', '공룡백과사전')
('104', '강기둥', None, '도서명미상')    

도서명에서 NULL 값이었던 곳에 '도서명미상'이 입력되었습니다.

쿼리의 조건절(2)-CASE

SELECT 이름,
CASE WHEN 대출일수_수정 > 5
	THEN '기간초과'
    	ELSE '기간내'
END AS 대출기간
FROM (
	SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정
    	FROM 도서대출내역2
);

query = '''
SELECT 이름,
CASE WHEN 대출일수_수정 > 5
	THEN '기간초과'
    	ELSE '기간내'
END AS 대출기간
FROM (
	SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정
    	FROM 도서대출내역2
);
'''
for row in c.execute(query):
	print(row)
>>>
('문강태', '기간초과')
('고문영', '기간초과')
('문상태', '기간초과')
('강기둥', '기간내')

Q&A

Q1. IFNULL(B.도서명, '도서명미상')을 CASE 문을 사용해서 동일하게 바꾼다면 어떻게 될까요?

CASE WHEN 도서명 IS NOT NULL
	THEN B.도서명
    	ELSE '도서명미상' END

CASE WHEN B.도서명 IS NULL
	THEN '도서명미상'
    	ELSE B.도서명 END

Q2. '도서명' 테이블과 '도서대출내역' 테이블을 도서ID를 key로 하여 LEFT JOIN해서 |도서ID|도서명|대출건수|대출상태| 항목을 출력하는 쿼리를 작성해 보세요.

• 대출건수 : 도서별로 도서대출내역 테이블에 대출일자가 있으면 대출건수 1로 본다. 여러 번 대출되었으면 대출된 회수만큼 합산된다.
• 대출상태 : 대출일자는 NOT NULL인데 반납일자가 NULL인 도서대출내역이 있으면 '대출중', 그렇지 않고 모든 대출내역에 반납일자가 명시되어 있으면 '보관중'으로 본다.

SELECT C.도서ID, C.도서명,
SUM(C.대출건수) AS 대출건수,
CASE SUM(C.대출건수)-SUM(C.반납건수)
WHEN 0 THEN '보관중' ELSE '대출중' END AS 대출상태
FROM (
	SELECT A.도서ID, A.도서명,
    	CASE WHEN B.대출일 IS NULL THEN 0 ELSE 1 END AS 대출건수,
        CASE WHEN B.반납일 IS NULL THEN 0 ELSE 1 END AS 반납건수 FROM 도서명 AS A
        LEFT JOIN 도서대출내역 AS B
        ON A.도서ID = B.도서ID ) AS C GROUP BY C.도서ID, C.도서명 ;

마지막 예제를 보니 SQL을 본격적으로 공부하면 정말 많이 고민해야 됨이 느껴지네요. 마지막이 다들 이해 되시나요?😨