11_SQL INNER JOIN 실습

1. 인트로

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

---

2. 실습 DB 준비

import sqlite3
import pandas as pd

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

# 학생 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT    NOT NULL,
        class_id INTEGER,
        grade   INTEGER
    )
""")

# 반 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS classes (
        id        INTEGER PRIMARY KEY AUTOINCREMENT,
        name      TEXT    NOT NULL,
        teacher   TEXT,
        room      TEXT
    )
""")

# 과목 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS subjects (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT    NOT NULL
    )
""")

# 성적 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS scores (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER,
        subject_id INTEGER,
        score      INTEGER,
        FOREIGN KEY (student_id) REFERENCES students(id),
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
    )
""")

conn.commit()

코드 설명:

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

---

3. 샘플 데이터 삽입

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

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

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

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

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

---

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

4.1. INNER JOIN이란?

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

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

4.2. 기본 INNER JOIN

# 학생 정보와 반 정보를 함께 조회
df = pd.read_sql("""
    SELECT s.name   AS 학생이름,
           c.name   AS 반이름,
           c.teacher AS 담임선생님
    FROM students AS s
    INNER JOIN classes AS c ON s.class_id = c.id
    ORDER BY c.name
""", conn)
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 활용

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

코드 설명:

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

4.4. DB 테이블 중첩 JOIN 실습

# 학생, 반, 성적, 과목을 모두 연결
df = pd.read_sql("""
    SELECT st.name  AS 학생,
           c.name   AS 반,
           su.name  AS 과목,
           sc.score AS 점수
    FROM scores     AS sc
    INNER JOIN students AS st ON sc.student_id = st.id
    INNER JOIN classes  AS c  ON st.class_id   = c.id
    INNER JOIN subjects AS su ON sc.subject_id  = su.id
    WHERE c.name = '1반'
    ORDER BY su.name
""", conn)
print("=== 1반 성적표 ===")
print(df)

코드 설명:

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

---

5. 집계 함수와 INNER JOIN 조합

# 학생별 평균 점수 계산
df = pd.read_sql("""
    SELECT st.name         AS 학생이름,
           AVG(sc.score)   AS 평균점수,
           MAX(sc.score)   AS 최고점수,
           MIN(sc.score)   AS 최저점수
    FROM scores     AS sc
    INNER JOIN students AS st ON sc.student_id = st.id
    GROUP BY st.id, st.name
    ORDER BY 평균점수 DESC
""", conn)
print("=== 학생별 성적 요약 ===")
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 쿼리를 실행한다.

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

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

코드 설명:

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

---

7. 문제풀기