DB - JOIN, SUBQUERY

✔ JOIN

• JOIN 주의사항
  • 조인의 처리는 어느 테이블을 먼저 읽을지 결정하는 것이 중요.(요즘의 경우 크게 신경쓸 필요가 없음. DB마다 최적화를 위한 옵티마이저가 있음.)
  • INNER JOIN : 어느 테이블을 먼저 읽어도 결과가 달라지지 않아 MySQL 옵티마이저가 조인의 순서를 조절해 다양한 방법으로 최적화 수행.
  • OUTER JOIN : OUTER가 되는 테이블을 먼저 읽어야 하므로 옵티마이저가 조인 순서를 선택할 수 없음.
• JOIN의 필요성 : 복수의 테이블의 필요한 데이터를 가져와 볼 수 있음.

1. JOIN의 종류

◾ INNER JOIN

• 가장 일반적인 JOIN. 교집합
• 동등 조인(Equi-Join)이라고도 하며, N개의 테이블 조인 시 N-1개의 조인 조건 필요.
• on 키워드로 JOIN 조건 명시.
  • 일반 조건은 where 사용.
• USING을 이용한 JOIN 조건 지정 : table이나 alias 명시 불가.
• default join : inner join은 생략할 수 있음

-- alias를 사용할 수 있음.
-- on 키워드 사용.
select col1, col2, ..., colN
from table1 INNER JOIN table2
on table1.col = table2.col;

-- using 사용.
select col1, col2, ..., colN
from table1 INNER JOIN table2
using (공통 column);

◾ NATURAL JOIN

• 공통 컬럼으로 자동 INNER JOIN.
  • 모든 공통 컬럼을 사용하므로 원하는 결과가 나오지 않을 수 있음.

select col1, col2, ..., colN
from table1 NATURAL JOIN table2;

◾ OUTER JOIN

• 한쪽 테이블에는 해당 데이터가 존재하는데 다른 쪽 테이블에는 존재하지 않을 경우 그 데이터가 존재하지 않는 문제점을 해결하기 위해 사용.
• [LEFT | RIGHT | FULL] OUTER JOIN으로 구분.
• LEFT OUTER JOIN : 왼쪽 테이블 기준으로 JOIN 조건에 일치하지 않는 데이터까지 출력.

SELECT col1, col2, ..., colN
FROM table1 LEFT OUTER JOIN talbe2
ON or USING;

• RIGHT OUTER JOIN : 오른쪽 테이블 기준으로 JOIN 조건에 일치하지 않는 데이터까지 출력.

SELECT col1, col2, ..., colN
FROM table1 RIGHT OUTER JOIN talbe2
ON or USING;

• FULL OUTER JOIN : 전체 테이블 기준으로 JOIN 조건에 일치하지 않는 데이터까지 출력.
  • 단, MySQL에서는 지원하지 않음.
  • LEFT, RIGHT를 UNION으로 합쳐 같은 효과 사용 가능.

SELECT col1, col2, ..., colN
FROM table1 FULL OUTER JOIN talbe2
ON or USING;

-- MySQL FULL OUTER JOIN
SELECT col1, col2, ..., colN
FROM table1 LEFT OUTER JOIN talbe2
ON or USING
UNION
SELECT col1, col2, ..., colN
FROM table1 RIGHT OUTER JOIN talbe2
ON or USING;

◾ SELF JOIN

• 같은 테이블끼리 JOIN

◾ None-Equi JOIN

• table의 PK, FK가 아닌 일반 column을 join조건으로 지정.

---

✔ SUBQUERY

• 서브 쿼리 : 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미.
  • 외부 쿼리, 메인 쿼리 : 서브 쿼리 포함.
  • 서브 쿼리, 내부 쿼리 : 외부 쿼리에 포함되는 서브 쿼리.
  • 비교 연산자의 오른쪽에 기술해야하고 괄호로 감싸야 함.

서브 쿼리 사용 이유 : join의 경우 쿼리가 복잡해지거나 카테시안곱으로 인한 속도 저하가 올 수 있음.(case by case)

◾ 서브 쿼리 종류

• 중첩 서브 쿼리(Nested Subquery) : WHERE 문에 작성하는 서브 쿼리.
  • 단일 행
  • 복수(다중) 행
  • 다중 컬럼
• 인라인 뷰(Inline View) : FROM 문에 작성하는 서브 쿼리.
• 스칼라 서브 쿼리(Scalar Subquery) : SELECT 문에 작성하는 서브 쿼리.

◾ 서브 쿼리 사용 가능한 곳

• SELECT
• FROM
• WHERE
• HAVING
• ORDER BY
• INSERT문의 VALUES
• UPDATE문의 SET

1. 중첩 서브 쿼리(Nested Subquery)

• WHERE절에 사용되는 서브 쿼리.

SELECT col1, col2, ..., colN
FROM table
WHERE (condition and subQuery)

◾ Nested Subquery - 단일행

• 서브 쿼리의 결과가 단일행 반환.

◾ Nested Subquery - 복수행

• 서브 쿼리의 결과가 다중행 반환. : IN, ANY, ALL

◾ Nested Subquery - 다중 컬럼

• 서브 쿼리의 결과가 다중열 반환.

2. 인라인 뷰(Inline View)

SELECT col1, col2, ..., colN
FROM sbuQuery
...

◾ 인라인 뷰(Inline View)

• FROM절에 사용되는 서브 쿼리.
• 뷰(View)처럼 결과가 동적으로 생성된 테이블로 사용 가능.
• 임시적인 뷰이므로 저장되지 않음.
• 동적으로 생성된 테이블이기 때문에 column 자유롭게 참조 가능.

◾ TopN 질의

• Top N 쿼리 : 상위 n개의 데이터를 추출하는 쿼리

set @pageno = 3;	-- 변수 설정

select b.rn, b.employee_id, b.first_name, b.salary
from (
	  select @rownum := @rownum + 1 as rn, a.*
	  from (
		    select employee_id, first_name, salary
		    from employees
		    order by salary desc
		   ) a, (select @rownum := 0) tmp
	 ) b
where b.rn > (@pageno * 5 - 5) and b.rn <= (@pageno * 5);

◾ LIMIT 활용 (MySQL)

• LIMIT start, end : start부터 end까지 행으로 제한.
  • start 생략시 기본값 0.

3. 스칼라 서브 쿼리(Scalar Subquery)

• SELECT절에 사용되는 서브 쿼리.
  • 한 개의 행만 반환.
  • 외부 쿼리의 컬럼도 사용 가능.

SELECT col1, col2, ..., colN, subQuery
FROM table
...

4. SUBQUERY 활용

◾ 서브 쿼리 - CREATE

CREATE TABLE 테이블명
subQuery

-- 구조만 생성 : 조회 결과 데이터가 0이 되도록 조건 설정.
CREATE TABLE 테이블명
SELECT * FROM 테이블명 WHERE 1 = 0;

◾ 서브 쿼리 - INSERT

INSERT INTO 테이블명
subQuery

◾ 서브 쿼리 - UPDATE

UPDATE 테이블명
SET 조건
WHERE (조건 and subQuery)

◾ 서브 쿼리 - DELETE

DELETE
FROM 테이블명
WHERE (조건 and subQuery)