Section 04. 테이블을 서로 통합하는 조인 알아보기

01. 조인

테이블을 2개 이상 조인한다는 의미

조인은 테이블 A의 열과 테이블 B의 열 정보를 조합해서 검색할 수 있게 해준다.

처음부터 같은정보를 저장하면 데이터가 중복 저장되는 문제와, 수정문제, 저장공간 문제가 발생한다.

내부 조인(INNER JOIN)

SELECT [열 이름]

FROM [테이블 1]

INNER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]

WHERE [검색 조건] 

 

ALTER TABLE EMP2

ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT2 (DCODE);

 

SELECT * FROM EMP2

INNER JOIN DEPT2 ON EMP2.DEPTNO = DEPT2.DCODE;

EMP2와 DEPT2테이블의 부서번호를 FK-PK 제약조건을 걸고

부서번호가 같은것들을 INNER JOIN 했더니 각 테이블이 가지고 있는 모든 열을 검색할 수 있었다.

 

조인할 떄 열 이름이 같으면 AS를 이용해서 별칭을 해야 구분해야한다.

오라클에서는 AS를 안써도 된다 ...

SELECT * FROM EMP2  e

INNER JOIN DEPt2  d

 

ON문은 조인할 떄 조인 조건을 위해 사용하며, WHERE 문은 조인을 완료한 상태에서 조건에 맞는 값을 가져오고자 사용.

 

3개이상의 테이블을 JOIN할 떄 데이터양이 적은 열을 조인 조건으로 우선 사용하는 것이 좋다. 검색 성능이 높아진다.

외부 조인(OUTER JOIN)

다른 테이블에 있는 행에서 일치 항목이 아닌 행을 조합해 검색해야 할 때도 있다. 그런경우에 OUTER JOIN을 사용.열의 일치 항목을 고려하지 않고, 한쪽 테이블을 다른 쪽 테이블에 조합할 떄 사용한다.

 

LEFT OUTER JOIN 문의 기본 개념 알아보기

왼쪽의 테이블을 기준으로 JOIN할 떄 왼쪽 테이블에 없는 것은 NULL 처리된다.

SELECT a.DEPTNO, b.DCODE

FROM EMP2 a

LEFT OUTER JOIN DEPT2 b ON a.DEPTNO = b.DCODE;

 

EMP2테이블 기준으로 DEPT2테이블을 LEFT OUTER JOIN 하였다.

EMP2가 가지고있는 DEPTNO지만 DEPT2는 없다면 위처럼 NULL이 나온다.

 

RIGHT OUTER JOIN 문의 기본 개념 알아보기

원리는 같고 방향은 반대이다.

SELECT a.DEPTNO, b.DCODE

FROM EMP2 a

RIGHT OUTER JOIN DEPT2 b ON a.DEPTNO = b.DCODE;

 

FULL OUTER JOIN 문의 기본 개념 알아보기

양쪽의 일치하지 않는 행까지 모두 검색한다.

SELECT a.DEPTNO, b.DCODE

FROM EMP2 a

FULL OUTER JOIN DEPT2 b ON a.DEPTNO = b.DCODE;

교차 조인(CROSS JOIN)

각 테이블을 모든 경우의 수로 조합한 데이터가 필요할 경우 사용하며, 카르테시안 곱 (Cartesian Product)라고도 한다.

셀프 조인(SELF JOIN)

같은 테이블을 사용하는 특수한 조인이다.

주의할 점으로는 반드시 별칭을 사용해야 한다는 것이다.

같은테이블을 사용해 계산이 필요할 떄 사용

SELECT a.PAY, b.PAY-5000000 diff

FROM EMP2 a

INNER JOIN EMP2 b

ON a.EMP_TYPE = '정규직';

02. 서브 쿼리

서브 쿼리 (Subquery)는 쿼리 안에 포함되는 또 다른 쿼리를 말한다.

서브 쿼리는 조인하지 않은 상태에서 다른 테이블과 일치하는 행을 찾거나, 조인 결과를 다시 조인할 떄 사용할 수 있다.

 

서브 쿼리의 특징

• 반드시 소괄호로 감싸 사용한다.
• 주 쿼리를 실행하기 전에 1번만 실행한다.
• 비교 연산자에 서브 쿼리를 사용하는 경우 서브 쿼리를 오른쪽에 기술해야 한다.
• 내부에는 정렬 구문인 ORDER BY 문을 사용할 수 없다.

WHERE 문에 서브 쿼리 사용하기

WHERE 문에 사용하는 서브 쿼리를 중첩 서브 쿼리 ( Nested Subquery ) 라고 부른다. 중첩 서브 쿼리는 조건문의 일부로 사용한다. 서브 쿼리를 비교 연산자와 함께 사용할 떄는 반드시 서브 쿼리의 반환 결과가 1건 이하여야 한다. 만약 서브 쿼리의 반환 결과가 2건 이상인 경우에는 비교 연산자가 아닌 다중 행연산자를 사용해야 한다.

 

다중 행 연산자	설명
IN	서브 쿼리의 결과에 존재하는 임의의 값과 같은 조건 검색
ANY	서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건 검색
EXISTS	서브 쿼리의 결과를 만족하는 값이 존재하는지 여부 확인
ALL	서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건 검색

단일 행 서브 쿼리

단일 행 서브 쿼리란 서브 쿼리의 결과가 1행만 반환되는 쿼리이다.

SELECT * FROM EMP2

WHERE NAME = (SELECT NAME FROM EMP2 WHERE NAME IN ('나사장'));

다중 행 서브 쿼리

IN, NOT IN문

SELECT * FROM EMP2

WHERE NAME IN (SELECT NAME FROM EMP2 WHERE NAME IN ('나사장','최오대','박지리'));

NOT IN 으로 포함하지 않는 것도 검색가능.

 

ANY 문

ANY 문과 함께 사용했으므로 서브 쿼리의 결괏값이 여개여도 일치하는 모든 행을 주 쿼리에서 검색해 반환할 수 있다.

SELECT * FROM EMP2

WHERE NAME IN (SELECT NAME FROM EMP2 WHERE NAME IN ('나사장','최오대','박지리'));

 

EXISTS 문, NOT EXISTS 문

조건의 결괏값이 한행이라도 있으면 TRUE가 되어 메인 쿼리 실행.

SELECT * FROM EMP2

WHERE EXISTS (SELECT NAME FROM EMP2 WHERE NAME IN ('나사장','ㅁ','ㅂ'));

 

NOT EXISTS는 하나라도 없으면 TRUE

 

ALL 문

모든 값을 만족하는 조건을 주 쿼리에서 검색해 결과를 반환

FROM 문에 서브 쿼리 사용하기

FROM문에 사용하는 서브 쿼리 결과는 조인할 수 있으므로 쿼리를 논리적으로 격리할 수 있다.

보통 인라인뷰(Inline View)라고 한다.

 

 

EMP2, DEPT2테이블을 확인해서 부서코드가 일치하는 행중에 서울지사를 갖고있는 행의 

EMP_TYPE, DEPTNO, AREA 검색 => 결과적으로 서울지사를 다니는 직원의 EMP_TYPE을 찾는 결과이다.

SELECT a.EMP_TYPE, a.DEPTNO, b.AREA

FROM EMP2 a

INNER JOIN (SELECT DCODE, AREA

FROM DEPT2

WHERE AREA = '서울지사'

) b ON a.DEPTNO = b.DCODE;

SELECT 문에 서브 쿼리 사용하기

SELECT 문에 사용하는 서브 쿼리는 반드시 1개의 행을 반환해야 하므로 SUM, COUNT, MIN, MAX 등의 집계 함수와 함께 사용되는 경우가 많다. 하지만 성능 저하로 사용하지 않는게 좋다.

SELECT 문에 사용된 서브 쿼리는 스칼라 서브 쿼리 (Scalar Subquery) 라고 부르며, 스칼라 서브쿼리는 1개 이상 사용 가능하다.

 

SELECT a.AREA,

(SELECT EMP_TYPE FROM EMP2 b WHERE b.DEPTNO = a.DCODE) AS EMP_TYPE

FROM DEPT2 a

WHERE a.AREA = '서울지사';

서브쿼리의 리턴은 1개의 행이어야한다. 즉 DEPTNO = DCODE 인 것이 1:1로 매칭이 되어야한다!!!!!!