11 SQL INNER JOIN 실습

1. 인트로

JOIN(조인)은 두 개 이상의 테이블을 연결하여 함께 조회하는 기능이다. 실제 서비스 DB는 데이터를 여러 테이블에 나눠 저장하기 때문에 JOIN은 필수 기술이다. 이번 차시에서는 INNER JOIN(이너 조인)을 중점적으로 익힌다.

2. 실습 DB 준비

1
import sqlite3
2
import pandas as pd
3

4
conn = sqlite3.connect("school.db")
5
cur  = conn.cursor()
6

7
# 학생 테이블
8
cur.execute("""
9
    CREATE TABLE IF NOT EXISTS students (
10
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
11
        name    TEXT    NOT NULL,
12
        class_id INTEGER,
13
        grade   INTEGER
14
    )
15
""")
16

17
# 반 테이블
18
cur.execute("""
19
    CREATE TABLE IF NOT EXISTS classes (
20
        id        INTEGER PRIMARY KEY AUTOINCREMENT,
21
        name      TEXT    NOT NULL,
22
        teacher   TEXT,
23
        room      TEXT
24
    )
25
""")
26

27
# 과목 테이블
28
cur.execute("""
29
    CREATE TABLE IF NOT EXISTS subjects (
30
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
31
        name TEXT    NOT NULL
32
    )
33
""")
34

35
# 성적 테이블
36
cur.execute("""
37
    CREATE TABLE IF NOT EXISTS scores (
38
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
39
        student_id INTEGER,
40
        subject_id INTEGER,
41
        score      INTEGER,
42
        FOREIGN KEY (student_id) REFERENCES students(id),
43
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
44
    )
45
""")
46

47
conn.commit()

코드 설명:

8~14행: students 테이블. class_id로 classes 테이블과 연결된다.
17~23행: classes 테이블. 반 정보를 저장한다.
26~30행: subjects 테이블. 과목 정보를 저장한다.
33~41행: scores 테이블. 학생 id와 과목 id를 외래키로 연결하여 성적을 저장한다.

3. 샘플 데이터 삽입

1
# 반 데이터
2
classes_data = [
3
    ("1반", "김선생님", "101호"),
4
    ("2반", "이선생님", "102호"),
5
    ("3반", "박선생님", "103호"),
6
]
7
cur.executemany("INSERT INTO classes (name,teacher,room) VALUES (?,?,?)", classes_data)
8

9
# 학생 데이터
10
students_data = [
11
    ("김철수", 1, 2),  # 1반, 2학년
12
    ("이영희", 1, 2),
13
    ("박지민", 2, 2),
14
    ("최수진", 2, 2),
15
    ("홍길동", 3, 2),
16
    ("강민준", 3, 2),
17
]
18
cur.executemany("INSERT INTO students (name,class_id,grade) VALUES (?,?,?)", students_data)
19

20
# 과목 데이터
21
subjects_data = [("국어",), ("영어",), ("수학",), ("과학",)]
22
cur.executemany("INSERT INTO subjects (name) VALUES (?)", subjects_data)
23

24
# 성적 데이터
25
scores_data = [
26
    (1, 1, 85), (1, 2, 90), (1, 3, 78),
27
    (2, 1, 92), (2, 2, 88), (2, 3, 95),
28
    (3, 1, 70), (3, 2, 75), (3, 3, 80),
29
    (4, 1, 88), (4, 2, 91), (4, 3, 86),
30
    (5, 1, 60), (5, 2, 65), (5, 3, 72),
31
    (6, 1, 95), (6, 2, 93), (6, 3, 97),
32
]
33
cur.executemany("INSERT INTO scores (student_id,subject_id,score) VALUES (?,?,?)", scores_data)
34

35
conn.commit()
36
print("샘플 데이터 삽입 완료")

4. INNER JOIN(이너 조인) 실습

4.1. INNER JOIN이란?

두 테이블에서 공통으로 일치하는 값이 있는 행만 연결하여 가져온다. 일치하지 않는 행은 결과에서 제외된다.

1
students ──(class_id = classes.id)──▶ classes
2
두 테이블 모두에 일치하는 값이 있는 경우만 결과에 포함

4.2. 기본 INNER JOIN

1
# 학생 정보와 반 정보를 함께 조회
2
df = pd.read_sql("""
3
    SELECT s.name   AS 학생이름,
4
           c.name   AS 반이름,
5
           c.teacher AS 담임선생님
6
    FROM students AS s
7
    INNER JOIN classes AS c ON s.class_id = c.id
8
    ORDER BY c.name
9
""", conn)
10
print(df)

코드 설명:

3행: AS 학생이름 — 컬럼의 별칭(Alias(앨리어스))을 지정한다. 결과에 표시될 이름이다.
6행: FROM students AS s — students 테이블을 s라는 짧은 별칭으로 사용한다.
7행: INNER JOIN classes AS c ON s.class_id = c.id — students의 class_id와 classes의 id가 같은 행을 연결한다.

4.3. DB 테이블 별명 지정 & USING 활용

1
# USING으로 JOIN (컬럼명이 같을 때 사용 가능)
2
# 참고: 컬럼명이 다르면 ON을 사용한다
3
df = pd.read_sql("""
4
    SELECT scores.score,
5
           students.name  AS 학생,
6
           subjects.name  AS 과목
7
    FROM scores
8
    INNER JOIN students ON scores.student_id = students.id
9
    INNER JOIN subjects ON scores.subject_id = subjects.id
10
    ORDER BY students.name, subjects.name
11
""", conn)
12
print(df)

코드 설명:

7~8행: scores 테이블을 두 테이블과 각각 JOIN한다. 이처럼 테이블 3개를 연결할 수 있다.
결과에는 성적, 학생 이름, 과목 이름이 함께 출력된다.

4.4. DB 테이블 중첩 JOIN 실습

1
# 학생, 반, 성적, 과목을 모두 연결
2
df = pd.read_sql("""
3
    SELECT st.name  AS 학생,
4
           c.name   AS 반,
5
           su.name  AS 과목,
6
           sc.score AS 점수
7
    FROM scores     AS sc
8
    INNER JOIN students AS st ON sc.student_id = st.id
9
    INNER JOIN classes  AS c  ON st.class_id   = c.id
10
    INNER JOIN subjects AS su ON sc.subject_id  = su.id
11
    WHERE c.name = '1반'
12
    ORDER BY su.name
13
""", conn)
14
print("=== 1반 성적표 ===")
15
print(df)

코드 설명:

7~10행: scores, students, classes, subjects 총 4개의 테이블을 연결한다.
11행: WHERE c.name = '1반' — 최종 결과에서 1반만 필터링한다.
이 한 번의 SQL로 4개 테이블의 정보를 통합하여 조회한다.

5. 집계 함수와 INNER JOIN 조합

1
# 학생별 평균 점수 계산
2
df = pd.read_sql("""
3
    SELECT st.name         AS 학생이름,
4
           AVG(sc.score)   AS 평균점수,
5
           MAX(sc.score)   AS 최고점수,
6
           MIN(sc.score)   AS 최저점수
7
    FROM scores     AS sc
8
    INNER JOIN students AS st ON sc.student_id = st.id
9
    GROUP BY st.id, st.name
10
    ORDER BY 평균점수 DESC
11
""", conn)
12
print("=== 학생별 성적 요약 ===")
13
print(df.round(1))

코드 설명:

4행: AVG(sc.score) — 각 학생의 성적 평균을 계산한다.
9행: GROUP BY st.id, st.name — 학생별로 묶어서 집계한다. (GROUP BY는 13차시에서 자세히 배운다.)
10행: ORDER BY 평균점수 DESC — 별칭(평균점수)으로도 정렬할 수 있다.

6. 활용 Plus — 리액트 연동 관점

리액트 학교 앱에서 “학생 성적표” 페이지를 만들 때, API 서버는 이 JOIN 쿼리를 실행한다.

1
def get_report(class_name):
2
    df = pd.read_sql("""
3
        SELECT st.name AS 학생, su.name AS 과목, sc.score AS 점수
4
        FROM scores AS sc
5
        INNER JOIN students AS st ON sc.student_id = st.id
6
        INNER JOIN classes  AS c  ON st.class_id   = c.id
7
        INNER JOIN subjects AS su ON sc.subject_id  = su.id
8
        WHERE c.name = ?
9
        ORDER BY st.name, su.name
10
    """, conn, params=(class_name,))
11
    return df.to_dict("records")
12

13
data = get_report("1반")
14
print(data[:3])

코드 설명:

10행: params=(class_name,) — pandas read_sql에서 파라미터를 전달하는 방법이다.
11행: .to_dict("records") — JSON 형태로 변환하여 리액트에 반환한다.

7. 문제풀기

두 테이블에서 JOIN 조건이 일치하는 행만 결과에 포함된다. 한쪽 테이블에만 있는 행은 제외된다.

테이블명이 길 때 짧은 별칭으로 코드를 간결하게 만들기 위해서이다. 또한 같은 테이블을 두 번 조인할 때 구분하기 위해서도 사용한다.

ON scores.student_id = students.id scores 테이블의 student_id가 students 테이블의 id와 같은 행을 연결한다.

각 JOIN마다 ON 조건을 정확하게 지정해야 한다. 잘못된 ON 조건은 카르테시안 곱(모든 행의 조합)을 만들어 엄청난 양의 잘못된 결과를 반환한다.