[SQL] 조인 / 서브쿼리 / WITH문

‣ 조인 JOIN

• 2개 이상의 테이블을 결합하여 한 번에 조회하는 방식이다.
• 각 테이블의 관계(1:M)를 이용해서 조인 조건을 만든다.
  • PK-FK 관계를 맺은 테이블끼리 조인을 이용해서 처리할 수 있다.

---

조인의 종류

1) 내부 조인 (Inner Join) ⭐
2) 외부 조인 (Outer Join)⭐
3) 크로스 조인 (Corss Join)
4) 셀프 조인 (self Join)
5) 자연 조인 (Natural Join)

---

조인 형식

• ANSI 구문 (표준 문법)

SELECT 테이블1.칼럼, 테이블2.칼럼, …
FROM 테이블1 JOIN 테이블2
ON 조인_조건

→ ON - 조인에서 쓰이는 조건문

---

• 오라클 구문

SELECT 테이블1.칼럼, 테이블2.칼럼, …
FROM 테이블1, 테이블2
WHERE 조인_조건

- 앞으로 나올 쿼리는 ANSI 구문을 위주로 표기하겠음.

---

드라이브 테이블 / 드리븐 테이블

1.드라이브 테이블
    1) 조인 관계를 처리하는 메인 테이블
    2) 1:M 관계에서 1에 해당하는 테이블
    3) 일반적으로 데이터의 갯수가 적다.
    4) PK를 조인 조건으로 사용하기 때문에 인덱스(INDEX) 사용이 가능하다. (빠르다는 의미)
2. 드리븐 테이블
    1) 1:M 관계에서 M에 해당하는 테이블
    2) 일반적으로 데이터의 갯수가 많다.
    3) FK를 조인 조건으로 사용하기 때문에 인덱스(INDEX) 사용이 불가능하다. (느리다는 의미)
3. 드라이브 테이블을 드리븐 테이블보다 먼저 작성하면 성능에 도움이 된다.

---

◊ 내부조인 INNER JOIN

• 2개 테이블에 공통으로 존재하는 데이터만 조회하는 방식

사용방법: ANSI 문법

SELECT 칼럼명
FROM 테이블명 A INNER JOIN 테이블명 B
ON A.공통칼럼명 = B.공통칼럼명;

• 오라클 문법은 ANSI에서 INNER JOIN을 ,(콤마)로 변경
  ON 을 WHERE로 활용

---

◊ 외부조인 OUTER JOIN

• 한 테이블의 내용은 모두 포함되고, 다른 한 테이블의 내용은 일치하는 정보만 포함됨
• 왼쪽 테이블의 내용을 모두 포함하는 경우
  '왼쪽 외부 조인(Left Outer Join)'이라고 함
• 오른쪽 테이블의 내용을 모두 포함하는 경우
  '오른쪽 외부 조인(Right Outer Join)'이라고

사용방법: ANSI 문법

• LEFT OUTER JOIN
  SELECT 칼럼명
  FROM 테이블명 A LEFT OUTER JOIN 테이블명 B
  ON A.공통칼럼명 = B.공통칼럼명;

• RIGHT OUTER JOIN
  SELECT 칼럼명
  FROM 테이블명 A RIGHT OUTER JOIN 테이블명 B
  ON A.공통칼럼명 = B.공통칼럼명;

• LEFT / RIGHT OUTER JOIN 에서 OUTER는 생략 가능함. (권장X)

---

3개 테이블 조인 방법

SELECT 칼럼명
FROM 테이블명 A INNER JOIN 테이블명 B
ON A.공통칼럼명 = B.공통칼럼명 INNER JOIN 테이블명 C
ON B.공통칼럼명 = C.공통칼럼명;

⇒표시된 부분을 통째로 하나의 테이블로 보고 뒤에 다시 새로운 조인을 연결해서 사용

---

💡내부 / 외부 조인을 표로 표현한 예시

---

◊ 셀프조인

• 하나의 테이블내에 있는 칼럼끼리 연결하는 조인 방식
• 조인 대상 테이블이 하나뿐이라는 것을 제외하면 일반적인 조인과 다를 바 없음
• 하나의 테이블에 각각 다른 별명(Alias)을 붙여서 처리

• 사용 방법

① SELECT 칼럼
FROM 테이블 A JOIN 테이블 B
ON A.칼럼 = B.칼럼;

② SELECT 칼럼
FROM 테이블 A, 테이블 B
WHERE A.칼럼 = B.칼럼;

---

◊ 크로스 조인

• 크로스 조인 (Cross Join)

• 두 개 테이블에서 연결 가능한 모든 행(Row)과 열(Column)을 결합

• 시뮬레이션 진행을 위해서 대용량의 테스트 데이터를 생성하는 경우 활용 가능

• 조인 조건을 생략하거나 잘못 작성하면 CROSS JOIN으로 처리됨

• 사용 방법
① SELECT 칼럼 FROM 테이블1 CROSS JOIN 테이블2;
② SELECT 칼럼 FROM 테이블1, 테이블2;

---

---

‣ 서브쿼리

서브쿼리(sub query)

1. 메인 쿼리에 포함되는 하위 쿼리를 서브쿼리라고 한다.

2. 서브쿼리를 먼저 실행해서 그 결과를 메인 쿼리에 전달한다.

• CREATE, INSERT, UPDATE, DELETE도 서브쿼리를 사용할 수 있다.

---

◊ 서브쿼리 종류

(SELECT가 출현하는 위치에 따라 서브쿼리를 부르는 방식이 달라짐)

1. 스칼라 서브쿼리 ⇒ SELECT 절에서 사용

• 상대적으로 많이 사용되진 않음
  (서브쿼리들 중에서 성능이 떨어짐)

2. 인라인 뷰 ⇒ FROM 절에서 사용

• FROM 절에 나오는 SELECT를 인라인 뷰(INLINE VIEW)라고 부름
• 정렬(ORDER BY)을 먼저 하고싶을 때 인라인 뷰를 씀

3. 일반 서브쿼리 ⇒ WHERE절에서 사용

• 단일 행 서브쿼리
• 다중 행 서브쿼리
  • 분리에 따라 연산자가 달라짐

---

◊ 단일 행 서브쿼리

(single row sub query)

1. 결과가 1행이다. (1개이다.)

2. 단일 행 서브쿼리인 경우
   1) WHERE 절에서 사용한 칼럼이 PK 또는 UNIQUE 칼럼인 경우
   (중복된 데이터를 저장하지 않는 칼럼을 이용해 동등비교를 한 경우)
   2) 통계 함수를 사용한 경우
   예시) SELECT COUNT(*) FROM EMPLOYEES

3. 단일 행 서브쿼리 연산자
   =, !=, >, >=, <, <=

---

◊ 다중 행 서브쿼리

(multiple row sub query)

1. 결과가 N행이다.
2. 다중 행 서브쿼리 연산자
   IN, ANY, ALL 등

---

❗주의❗서브쿼리 오류 발생

• 다중 행 서브쿼리와 단일 행 비교연산자(=, !=, >, >=, <, <=)를 함께 사용하는 경우
• 서브쿼리가 반환하는 칼럼의 수와 메인쿼리에서 비교되는 칼럼의 수가 다른 경우
• 서브쿼리의 결과가 NULL 인 경우

---

---

‣ WITH 문

1. 자주 사용하거나 복잡한 쿼리문을 WITH 절의 코드 블록으로 등록시켜 놓을 수 있다.
2. WITH 절의 코드 블록은 임시로 저장 되기 때문에 곧바로 사용해야 한다.
3. 쿼리문의 가독성이 좋아진다. (성능 향상에 관련 X)

---

⇢WITH문 사용 예제

---

• 서브쿼리와 WITH문 비교 사용

-- 1. 1 ~ 10번째로 고용된 사원 조회하기
-- 1) 서브쿼리
SELECT EMPLOYEE_ID, HIRE_DATE
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE
          FROM EMPLOYEES)
 WHERE RN BETWEEN 1 AND 10;


-- 2) WITH
WITH
    MY_SUBQUERY AS (
        SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE
          FROM EMPLOYEES
)
SELECT EMPLOYEE_ID, HIRE_DATE
  FROM MY_SUBQUERY
 WHERE RN BETWEEN 1 AND 10;

---

• 조인과 WITH문 비교 사용

-- 2. 부서별 부서번호, 부서명, 연봉총액을 조회하기
-- 1) 조인
SELECT MY.DEPARTMENT_ID
     , MY.TOTAL_SALARY
     , D.DEPARTMENT_NAME
  FROM DEPARTMENTS D INNER JOIN (SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
                                   FROM EMPLOYEES
                                  GROUP BY DEPARTMENT_ID) MY
    ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;

-- 2) WITH
WITH MY_SUBQUERY AS (
    SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
      FROM EMPLOYEES
     GROUP BY DEPARTMENT_ID
)
SELECT MY.DEPARTMENT_ID
     , D.DEPARTMENT_NAME
     , MY.TOTAL_SALARY
  FROM DEPARTMENTS D INNER JOIN MY_SUBQUERY MY
    ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;

---