데이터베이스 예제코드 (MySQL+AWS) - JOIN 구문 익히기

8. JOIN 구문 익히기

  • join은 두개 이상의 테이블로부터 필요한 데이터를 연결해 하나의 포괄적인 구조로 결합시키는 연산
  • INNER JOIN
  • OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN

실습 환경 맞추기

  • Mysql Workbench (도커 환경이 아닌 분들은 다음 SQL로 customer 데이터베이스를 만들고, 데이터를 넣어야 합니다.)
    • File -> Open SQL Script -> 03_data/customer/customer_join.sql
db.close() # 이전에 연결된 데이터베이스 끊어주세요 (에러나면 이미 끊어진 것임)
host_name = "0.0.0.0"
username = "root"
password = "korea123"
database_name = "customer"    # 데이터베이스 이름을 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'
)

8.1 INNER JOIN

  • INNER JOIN은 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력
df = pd.read_sql("SHOW TABLES;", db)
df
Out[75]:
Tables_in_customer
0 customer_table
1 order_table
df = pd.read_sql("SELECT * FROM customer_table", db)
df
Out[77]:
id customer_id customer_name
0 1 1 Robert
1 2 2 Peter
2 3 3 Smith
df = pd.read_sql("SELECT * FROM order_table", db)
df
Out[79]:
id order_id customer_id order_date
0 1 100 1 2017-10-23 04:42:17
1 2 200 4 2017-10-24 04:42:17
2 3 300 2 2017-10-25 04:42:17
  • 테이블 이름 다음에 한칸 띄고 새로운 이름을 쓰면, SQL구문 안에서 해당 이름으로 해당 테이블을 가리킬 수 있음 (AS 용법과 동일함)
SELECT * FROM customer_table C INNER JOIN order_table O ON C.customer_id = O.customer_id
  • 위 구문에서 C는 customer_table, O는 order_table 이 됨
    • C.customer_id = O.customer_id 와 같이 SQL 구문 안에서 특정 테이블의 컬럼을 가리키기 위해 사용됨
  • 다음과 같이 써도 MySQL은 동작 (HIVE SQL등 몇몇 DB는 미지원, 내부적으로는 동일한 JOIN 오퍼레이션, 단, JOIN을 쓰는 것이 가독성이 높음)
df = pd.read_sql("SELECT * FROM customer_table C INNER JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[81]:
id customer_id customer_name id order_id customer_id order_date
0 1 1 Robert 1 100 1 2017-10-23 04:42:17
1 2 2 Peter 3 300 2 2017-10-25 04:42:17
df = pd.read_sql("SELECT * FROM customer_table, order_table WHERE customer_table.customer_id = order_table.customer_id", db)
df
Out[83]:
id customer_id customer_name id order_id customer_id order_date
0 1 1 Robert 1 100 1 2017-10-23 04:42:17
1 2 2 Peter 3 300 2 2017-10-25 04:42:17
df = pd.read_sql("SELECT C.customer_name FROM customer_table C INNER JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[85]:
customer_name
0 Robert
1 Peter
  • JOIN은 INNER JOIN을 의미함
df = pd.read_sql("SELECT * FROM customer_table C JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[87]:
id customer_id customer_name id order_id customer_id order_date
0 1 1 Robert 1 100 1 2017-10-23 04:42:17
1 2 2 Peter 3 300 2 2017-10-25 04:42:17

8.2 OUTER JOIN

  • OUTER JOIN은 조인하는 테이블의 ON 절의 조건 중 한쪽의 데이터를 모두 가져옴
  • OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN 이 있음

8.2.1 LEFT OUTER JOIN

df = pd.read_sql("SELECT * FROM customer_table C LEFT OUTER JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[89]:
id customer_id customer_name id order_id customer_id order_date
0 1 1 Robert 1.0 100.0 1.0 2017-10-23 04:42:17
1 2 2 Peter 3.0 300.0 2.0 2017-10-25 04:42:17
2 3 3 Smith NaN NaN NaN NaT
SQL = """
    SELECT * 
    FROM customer_table C 
    LEFT OUTER JOIN order_table O ON C.customer_id = O.customer_id 
    WHERE O.order_id IS NULL
"""
df = pd.read_sql(SQL, db)
df
Out[94]:
id customer_id customer_name id order_id customer_id order_date
0 3 3 Smith None None None None

SQL = """
    SELECT * FROM order_table O 
    LEFT OUTER JOIN customer_table C 
    ON C.customer_id = O.customer_id
"""

df = pd.read_sql(SQL, db)
df
Out[96]:
id order_id customer_id order_date id customer_id customer_name
0 1 100 1 2017-10-23 04:42:17 1.0 1.0 Robert
1 3 300 2 2017-10-25 04:42:17 2.0 2.0 Peter
2 2 200 4 2017-10-24 04:42:17 NaN NaN None

8.2.2 RIGHT OUTER JOIN

df = pd.read_sql("SELECT * FROM customer_table C RIGHT OUTER JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[50]:
id customer_id customer_name id order_id customer_id order_date
0 1.0 1.0 Robert 1 100 1 2017-10-22 02:35:50
1 2.0 2.0 Peter 3 300 2 2017-10-24 02:35:50
2 NaN NaN None 2 200 4 2017-10-23 02:35:50
  • RIGHT JOIN 은 RIGHT OUTER JOIN을 의미
df = pd.read_sql("SELECT * FROM customer_table C RIGHT JOIN order_table O ON C.customer_id = O.customer_id", db)
df
Out[52]:
id customer_id customer_name id order_id customer_id order_date
0 1.0 1.0 Robert 1 100 1 2017-10-22 02:35:50
1 2.0 2.0 Peter 3 300 2 2017-10-24 02:35:50
2 NaN NaN None 2 200 4 2017-10-23 02:35:50