Node 05. 여러 개의 테이블 사용하기

5-1. 들어가며

---

• 학습 목표
  • 다수 테이블에 적재된 데이터를 조인해 조회하기
  • 여러 테이블에서 필요로 하는 데이터의 조인하기
  • UNION을 이용한 테이블 이어붙이기

5-2. 기본키(PK), 외래키(FK)

---

기본키(PK : Primary Key)

• 테이블은 무조건 1개의 기본키
• DB는 다수의 테이블로 구성되어 -> key를 매개체로 테이블을 연결!
• PK는 1개의 컬럼 -> 이 데이터는 테이블 내 유일성 보장!
• NULL 값 허용 ❌(∵ 필수값이기 때문)

테이블이 하나라면?

• 값 수정 시 모든 데이터를 수정해야 하는 번거로움.
  • 관리 목적에 따라서 테이블을 구분해주는 것이 잘 설계된 DB!

• orders 데이터로 기본키 파악하기
  

외래키(FK : Foreign Key)

• JOIN으로 타 테이블과 연결하는 경우 타 테이블의 PK를 참조!
• 외래키는 참조하는 PK 값과 일치 or NULL 값

• customers 및 employees 데이터로 PK와 FK 살펴보기
  

5-3. 다중 테이블 사용하기(JOIN)

---

JOIN

• 이름처럼, 테이블 내 행들을 논리적으로 연결하는 문법
• JOIN은 1개 이상의 테이블과 테이블을 연결해 사용하는 것!
• 각 테이블은 특정 규칙에 따라 관계를 맺는 것
• 조인 유형 미지정 : 기본적으로 INNER JOIN(내부조인)
• 두 테이블 조인 시 열 이름 동일 -> ON 대신 USING 사용 가능!

• 2개 테이블 JOIN

  SELECT 컬럼명
  FROM 테이블명 AS A
  JOIN 테이블명 AS B
  ON A.id = B.id

  [작성 조건]

  • SELECT절 : 출력할 컬럼명
  • FROM절 : 출력할 테이블명
    • 주로 관용적으로 테이블에 별칭을 주어 사용함!
  • ON : 테이블 합치는 기준 작성

  • order와 customers 2개 테이블 JOIN 해보기
    

• 3개 테이블 JOIN

  SELECT 컬럼명
  FROM 테이블명 AS A
  JOIN 테이블명 AS B
  ON A.id = B.id
  JOIN 테이블명 AS C
  ON A.id = C.id

  [작성 조건]

  • 2개 테이블과 동일
  • 단, 먼저 2개의 테이블 선 JOIN 후
    -> 결과 확인
    -> 그 후에 나머지 1개 테이블을 JOIN하는 것이 좋음!

  • order와 customers, employees 3개 테이블 JOIN 해보기
    

JOIN 종류 4가지

• INNER JOIN : 공통 부분(내부 조인)
• OUTER JOIN : 외부 조인
  • LEFT JOIN : 왼쪽 테이블이 기준!
  • RIGHT JOIN : 오른쪽 테이블이 기준!

기준을 삼는 테이블에 따른 결과가 나오는 것

• INNER JOIN

  • 기본 조인
  • 단순 JOIN 키워드로 동작
  • ON 뒤에 공통 KEY 기준 JOIN
  • 교집합의 의미(공통 부분만 출력)
  • ON 조건 충족 데이터만 가져옴!
  • 중심 테이블 선작성(FROM절 작성시에 로직 파악 수월)
  • JOIN 테이블이 다수일 경우, 1번의 JOIN마다 결과 확인하기!
  • 테이블에 별칭 사용 가능 💭 별칭을 사용하면 어떤 장점이 있을까? - 긴 이름 축약, 가독성 향상, 효율적인 쿼리문 작성

  

• OUTER JOIN(LEFT JOIN, RIGHT JOIN)
  • LEFT (OUTER) JOIN
    • 왼쪽 테이블은 유지, 오른쪽 테이블 데이터 끌어오기
      • 왼쪽 테이블 값은 모두 나오고, 오른쪽 테이블은 없는 값이라면 그 값은 NULL로 채워짐!
    • 현업에서는 JOIN과 LEFT JOIN 많이 사용
      
      
      

• RIGHT (OUTER) JOIN
  • 오른쪽 테이블은 유지, 왼쪽 테이블 데이터 끌어오기
    • 오른쪽 테이블 값은 모두 나오고, 왼쪽 테이블은 없는 값이라면 그 값은 NULL로 채워짐!
  • pandasql에서는 RIGHT JOIN 사용 시 에러 발생!
    

5-4. 데이터 이어붙이기(UNION)

---

UNION

• 테이블을 아래로(세로로) 이어붙이기!

• 데이터를 합쳐 새로운 행 생성

• 데이터가 너무 많다면 -> 쪼갠 뒤 관리 가능
  

• 사용 시 유의점

  • 테이블 간 달라도 되는 부분 : 컬럼명
  • 다르면 안되는 부분 : 컬럼 수, 데이터 타입
  • 여러 테이블 UNION 사용 시 포맷 반복

• 완전한 쿼리문 2개를 중복을 포함해 UNION으로 합친다면?

SELECT *
FROM 테이블1
UNION ALL 
SELECT *
FROM 테이블2

• 2020년, 2021년 테이블 합치기
  

• 2020년 및 2021년 테이블 합치기 + 특정 컬럼만 추출하기

  • 추출할 컬럼 : work_year, experience_level, job_title
    

• 중복 제거

  • 두 테이블의 데이터를 합칠 때 중복 제거하기

    pandasql의 경우 타 DBMS와는 달리 UNION DISTINICT 사용 불가(에러 발생)

  SELECT *
  FROM 테이블1
  UNION 
  SELECT *
  FROM 테이블2

• 2020년 테이블 및 2021년 테이블 중복 제거 후 합치기

  • 추출할 특정 칼럼 : work_year, experience_level, job_title
    

5-5. 서브쿼리 원리와 방식(Subquery)

---

Subquery

• 하나의 쿼리문 안에 -> 포함되는 또다른 쿼리문! == 중첩 쿼리(쿼리 재사용)

  즉, 메인 쿼리 > 서브 쿼리 포함하는 종속적 관계

• 하나의 중첩된 쿼리문으로 간편히 결과 얻기 가능
• 새로운 컬럼이 필요하지 않을 때 서브쿼리 사용!(JOIN과의 차이점)
  • 완전 다른 테이블에서 데이터 값을 조회 -> 그 결과를 메인쿼리 조건을 사용할 때 주로 사용

• 실행 순서
  • 서브쿼리 실행 후 -> 메인 쿼리 실행!

  SELECT *
  FROM 테이블명
  WHERE 컬럼명 IN (SELECT 컬럼명
                FROM 테이블명)

• 서브쿼리 특징
  • ( )로 묶어 사용
  • 연산자 오른쪽!
  • 서브 쿼리 내 : ORDER BY 사용 ❌
  • 괄호 뒤에는 ; 사용 ❌

WHERE절 서브쿼리

• 단일행 서브쿼리와 다중행 서브쿼리가 있음!

• 서브 쿼리 결과 == 메인쿼리 조건

  • 가장 작은 주문 금액 보기
    

  • 이 결과를 이용해 가장 작은 주문금액보다 작은 한도를 가진 고객 정보 보기

  

• 단일행 서브쿼리

  • 서브쿼리 실행 결과 -> 항상 1 이하
  • 단일행 비교연산자(=, !=, >, < 등) 사용

  SELECT 컬럼명
  FROM 테이블명
  WHERE 컬럼명 = (SELECT 컬럼명
                FROM 테이블명 
                WHERE 조건절)

  • 가장 작은 주문금액보다 작은 한도인 고객 정보
    

• 다중행 서브쿼리

  • 서브쿼리의 실행 결과 -> 여러 개
  • 다중행 비교연산자(IN, NOT IN 등) 사용

  SELECT 컬럼명
  FROM 테이블명
  WHERE 컬럼명 IN (SELECT 컬럼명
                           FROM 테이블명
                           WHERE 조건절)

  • 미국 거주 고객 주문번호 확인
    

FROM절 서브쿼리

• 인라인뷰(inline view)

  • 뷰처럼 작동해서 붙인 단어

    💬 View(뷰)? : 가상 테이블(논리적 테이블, 물리적 테이블이 아님)

  • 데이터 추출 결과를 1개의 테이블처럼 사용하기 위해서!
    • 특정 조건식을 갖는 데이터 추출 결과를 -> 테이블처럼 사용할 수 있게 되는 것
  • 서브쿼리 이용 시 최대 금액 제품을 구매한 구매자까지 확인 가능

• 고객별 마지막 제품 구매일자 확인
  

• 인라인 뷰 + julianday 사용

  • 고객별 마지막 제품 구매일자가 현재일 기준 얼마나 지났는지 보기

    MySQL : DATEDIFF 함수 등을 사용
    pandasql, SQLite : julianday 함수 등을 사용