데이터베이스 (MySQL+AWS) - 서브 쿼리 (MySQL SubQuery)

3. 서브 쿼리 (MySQL SubQuery)

  • DB에 접근하는 속도를 높이기 위해서, 테이블을 다른 테이블과 관계를 맺어 만듬
  • 한 테이블에 많은 정보가 들어 있으면, 속도가 느려짐
  • 다른 테이블에 있는 정보를 가져와 현재 테이블에서 찾고자 할 때, 서브 쿼리가 필요하다.
  • 서브쿼리는 SQL 절에서 계산필드, FROM, WHERE, HAVING에 들어갈 수 있으며, 대개 WHERE 절에서 많이 쓰임

3.1. 서브쿼리(Sub Query) 와 JOIN

  • JOIN은 출력 결과에 여러 테이블의 열이 필요한 경우 유용
  • 대부분의 서브쿼리(Sub Query) 는 JOIN 문으로 처리가 가능

3.2. 서브쿼리가 사용이 가능한 곳

  • SELECT
  • FROM
  • WHERE
  • HAVING
  • ORDER BY
  • INSERT문의 VALUES
  • UPDATE문의 SET
import pymysql
import pandas as pd
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sakila"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)
SQL = "SELECT * FROM film_category LIMIT 1"
df = pd.read_sql(SQL, db)
df
Out[6]:
film_id category_id last_update
0 1 6 2006-02-15 05:07:09
SQL = "SELECT * FROM category LIMIT 1"
df = pd.read_sql(SQL, db)
df
Out[7]:
category_id name last_update
0 1 Action 2006-02-15 04:46:27

3.3. 카테고리가 Comedy 인 데이터의 film id 출력하기

# JOIN 을 사용한 쿼리
SQL_QUERY = """
    SELECT film_id 
    FROM film_category
    JOIN category ON category.category_id = film_category.category_id
    WHERE category.name = 'Comedy'
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[9]:
film_id
0 7

3.4. 동일 쿼리를 서브 쿼리로 바꿔서 해봅니다.

# SUB QUERY 를 사용한 쿼리로 바꿔보자1 (category_id 는 어디 테이블의 카테고리 아이디일까?)
# 컬럼값 IN 서브쿼리출력값 -> 컬럼값과 서브쿼리값이 같을 때 
SQL_QUERY = """
    SELECT film_id 
    FROM film_category
    WHERE film_category.category_id IN
        (SELECT category.category_id FROM category WHERE category.name = 'Comedy')
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[13]:
film_id
0 7

3.5. 이렇게도 가능합니다.

# SUB QUERY 를 사용한 쿼리로 바꿔보자2 (category_id 는 어디 테이블의 카테고리 아이디일까?)
# 컬럼값 = 서브쿼리출력값 -> 컬럼값과 서브쿼리값이 같을 때 (비교 연산자 가능) 
SQL_QUERY = """
    SELECT film_id 
    FROM film_category
    WHERE film_category.category_id =
        (SELECT category.category_id FROM category WHERE category.name = 'Comedy')
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[11]:
film_id
0 7

3.6. 카테고리 이름이 Comedy 인 category_id 보다 더 높은 category_id를 가진 영화의 수를 category_id 와 함께 그룹별로 출력하기 (서브쿼리 활용)

# 컬럼값 > 서브쿼리출력값 -> 컬럼값과 서브쿼리값이 같을 때 (비교 연산자 가능) 
SQL_QUERY = """
    SELECT * FROM category
"""
pd.read_sql(SQL_QUERY, db)
Out[9]:
category_id name last_update
0 1 Action 2006-02-15 04:46:27
1 2 Animation 2006-02-15 04:46:27
2 3 Children 2006-02-15 04:46:27
3 4 Classics 2006-02-15 04:46:27
4 5 Comedy 2006-02-15 04:46:27
5 6 Documentary 2006-02-15 04:46:27
6 7 Drama 2006-02-15 04:46:27
7 8 Family 2006-02-15 04:46:27
8 9 Foreign 2006-02-15 04:46:27
9 10 Games 2006-02-15 04:46:27
10 11 Horror 2006-02-15 04:46:27
11 12 Music 2006-02-15 04:46:27
12 13 New 2006-02-15 04:46:27
13 14 Sci-Fi 2006-02-15 04:46:27
14 15 Sports 2006-02-15 04:46:27
15 16 Travel 2006-02-15 04:46:27

3.7. category_id가 가장 높은 카테고리에 포함된 영화 중 가장 높은 film_id를 가진 영화의 film_id 를 출력하자.

SQL_QUERY = """
    SELECT MAX(film_id) FROM film_category
    WHERE film_category.category_id IN
        (SELECT MAX(category.category_id) FROM category)
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[25]:
MAX(film_id)
0 989

category 에서 name 이 Comedy 인 category_id의 film_category 데이터를 한개 출력

SQL_QUERY = """
    SELECT * FROM film_category
    WHERE film_category.category_id IN
        (SELECT category.category_id FROM category WHERE category.name = 'Comedy')
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[17]:
film_id category_id last_update
0 7 5 2006-02-15 05:07:09

category_id가 가장 높은 카테고리 출력하기

SQL_QUERY = """
    SELECT * FROM category
    WHERE category_id IN
        (SELECT MAX(category.category_id) FROM category)
    LIMIT 1
"""
pd.read_sql(SQL_QUERY, db)
Out[18]:
category_id name last_update
0 16 Travel 2006-02-15 04:46:27
### 카테고리 이름이 Comedy 인 category_id 보다 더 높은 category_id를 가진 영화의 수를 category_id 와 함께 그룹별로 출력하기 (서브쿼리 활용)
# 컬럼값 > 서브쿼리출력값 -> 컬럼값과 서브쿼리값이 같을 때 (비교 연산자 가능) 
SQL_QUERY = """
    SELECT category_id, COUNT(*) AS film_count FROM film_category
    WHERE film_category.category_id >
        (SELECT category.category_id FROM category WHERE category.name = 'Comedy')
    GROUP BY film_category.category_id
"""
pd.read_sql(SQL_QUERY, db)
Out[10]:
category_id film_count
0 6 68
1 7 62
2 8 69
3 9 73
4 10 61
5 11 56
6 12 51
7 13 63
8 14 61
9 15 74
10 16 57