해시 조인은 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건(WHERE구)에 해당하는 행을 선택함
해당 행이 선택되면 조인 키(Join key)를 기준으로 해시 함수를 사용해 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾음
후행 테이블의 조인 키를 사용해 해시 함수를 적용해 해당 버킷을 검색함
(3) INTERSECT 연산
INTERSECT 연산은 두 개의 테이블의 특정 칼럼을 기준으로 교집합을 조회함
SELECT DEPTNO FROM DEPT
INTERSECT
SELECT DEPTNO_ID FROM EMP;
다음과 같이 두 개의 테이블의 특정 칼럼을 지정해서 INTERSECT 연산을 해 공통된 값을 조회함
02. Non-Equi JOIN
두 개의 테이블 간에 조인하는 경우 "="를 사용하지 않고 ">", "<" 등의 연산자를 사용하는 것
즉, 정확하게 일치하지 않는 것을 조인하는 것임
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO_ID < DEPT.DEPTNO;
위와 같이 두 기준 칼럼을 비교할 때 다음과 같은 연산자를 사용하면 각 레코드마다 기준에 해당하는 모든 레코드를 조인해 반환함
03. OUTER JOIN
두 개의 테이블 간에 교집합(EQUI JOIN)을 수행하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회하는 것
한쪽 테이블에만 있는 값 중에 반대편 값은 없으므로 NULL값들이 반환됨
왼쪽 테이블에만 있는 행을 포함하면 LEFT OUTER JOIN, 오른쪽 테이블에만 있는 행을 포함하면 RIGHT OUTER JOIN
FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 모두 수행하는 것
오라클에서는 OUTER JOIN을 할 때 "(+)" 를 이용해 할 수 있음
(1) LEFT OUTER JOIN과 RIGHT OUTER JOIN
LEFT OUTER JOIN
-- ANSI TYPE JOIN
SELECT * FROM EMP
LEFT OUTER JOIN DEPT
ON EMP.DEPTNO_ID = DEPT.DEPTNO;
-- ORACLE TYPE JOIN
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO_ID = DEPT.DEPTNO(+);
RIGHT OUTER JOIN
-- ANSI TYPE JOIN
SELECT * FROM EMP
RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO_ID = DEPT.DEPTNO;
-- ORACLE TYPE JOIN
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO_ID(+) = DEPT.DEPTNO;
04. CROSS JOIN
조인 조건구 없이 2개의 테이블을 하나로 조인
조인구가 없으므로 모든 경우의 수를 따지는 카테시안 곱이 발생
A테이블에 14개의 행, B테이블에 4개의 행이 있을 때 두 테이블을 조인하면 56개의 행이 조회
-- ANSI TYPE JOIN
SELECT * FROM EMP
CROSS JOIN DEPT;
-- ORACLE TYPE JOIN
SELECT * FROM EMP, DEPT;
05. UNION (합집합)
두 개의 테이블을 하나로 만드는 연산
두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식이 모두 일치해야 UNION할 수 있다. 그렇지 않으면 오류가 발생함
(1) UNION
두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거함
따라서 정렬 과정을 발생시킴
CREATE TABLE EMP_2
(
DIFF_NO NUMBER(10) NOT NULL,
FRIEND VARCHAR2(20),
DEPTNO_ID NUMBER(10),
SAL NUMBER(20),
CONSTRAINT A_FK FOREIGN KEY (DEPTNO_ID) REFERENCES DEPT(DEPTNO)
);
INSERT INTO EMP_2 VALUES (10, '구리구리', 1000, 2000);
INSERT INTO EMP_2 VALUES (4, '이수진', 1000, 4000);
SELECT * FROM EMP
UNION
SELECT * FROM EMP_2;
EMP_2 테이블을 만들고 칼럼명을 EMP와는 다른게, 그리고 '이수진' 레코드는 EMP의 레코드와 똑같게 넣어줬다.
UNION을 수행한 경과는 기준이 되는 EMP의 칼럼명으로 출력되며, 이수진 레코드는 중복을 제거해 한번만 출력됐다
(2) UNION ALL
두 개의 테이블을 하나로 합치면서 중복을 제거하지 않음
따라서 정렬을 유발하지 않음
SELECT * FROM EMP_2
UNION ALL
SELECT * FROM EMP;
UNION ALL을 수행했을 때 EMP_2를 기준으로 했으므로 EMP_2의 칼럼명으로 출력됐으며, '이수진'이 중복해서 출력됐다.
06. MINUS (차집합)
선행하는 SELECT문에는 있고 후행하는 SELECT문에는 없는 집합을 조회
MS-SQL에는 EXCEPT연산이 있음
MySQL에는 MINUS나 EXCEPT가 없으므로 서브쿼리나 조인을 통해 직접 구현해야 함. LEFT JOIN을 사용하면 구현할 수 있음
SELECT * FROM EMP
MINUS
SELECT * FROM EMP_2;
EMP를 기준으로 EMP_2와 MINUS 연산을 수행했을 때 '이수진'을 제외한 레코드가 출력됐다
2. 계층형 조회 (CONNECT BY)
계층형 조회는 오라클 데이터베이스에서 지원하는 것으로 어떤 트리 형태의 특정 데이터가 있다면 정방향, 혹은 역방향으로 조회할 수 있음
CREATE TABLE EMP
(
EMPNO NUMBER(10) PRIMARY KEY,
ENAME VARCHAR2(20),
MANAGER NUMBER(10),
SAL NUMBER(20)
);
INSERT INTO EMP VALUES (1000, '김대표이사', NULL, 25000);
INSERT INTO EMP VALUES (1001, '박전무', 1000, 17000);
INSERT INTO EMP VALUES (1002, '이전무', 1000, 15000);
INSERT INTO EMP VALUES (1003, '최상무', 1000, 14000);
INSERT INTO EMP VALUES (1004, '노팀장', 1001, 10000);
INSERT INTO EMP VALUES (1005, '유팀장', 1001, 11000);
INSERT INTO EMP VALUES (1006, '고팀장', 1002, 9000);
INSERT INTO EMP VALUES (1007, '최팀장', 1003, 6000);
INSERT INTO EMP VALUES (1008, '한책임', 1004, 5000);
INSERT INTO EMP VALUES (1009, '류책임', 1005, 5500);
INSERT INTO EMP VALUES (1010, '제갈책임', 1006, 5000);
INSERT INTO EMP VALUES (1011, '박선임', 1008, 4500);
INSERT INTO EMP VALUES (1012, '노선임', 1008, 4200);
INSERT INTO EMP VALUES (1013, '성선임', 1009, 4500);
INSERT INTO EMP VALUES (1014, '이사원', 1012, 3800);
INSERT INTO EMP VALUES (1015, '류사원', 1013, 4000);
INSERT INTO EMP VALUES (1016, '방사원', 1011, 4100);
SELECT LEVEL, LPAD(' ', 4 * (LEVEL - 1) ) || EMPNO, ENAME, MANAGER, SAL FROM EMP
START WITH MANAGER IS NULL
CONNECT BY PRIOR EMPNO = MANAGER;
START WITH는 시작조건, CONNECT BY PRIOR은 조인 조건이다. MANAGER가 NULL인 김대표이사가 ROOT노드이며 ROOT노드부터 시작해 정방향으로 노드를 탐색한다.
PRIOR은 직전 행을 의미하며 PRIOR EMPNO 즉 직전 EMPNO가 현재 행의 MANAGER와 같으면 집어넣겠다는 뜻이므로 계층적인 탐색이 가능하다
LPAD(채울 문자열, 문자열 총 길이)는 왼쪽에 문자열 총 길이에서 남은 개수만큼 문자열로 채운다
역방향 질의
SELECT LEVEL, LPAD(' ', 4 * (LEVEL - 1) ) || EMPNO, ENAME, MANAGER, SAL FROM EMP
START WITH EMPNO = 1015
CONNECT BY PRIOR MANAGER = EMPNO;
직전 행의 MANAGER가 현재 행의 EMPNO와 같을 때이므로 역방향으로 질의가 수행됨
3. 서브쿼리 (Subquery)
01. Main query와 Subquery
서브쿼리는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문
FROM구에 사용되는 SELECT문 = 인라인 뷰(Inline View)
SELECT문에 사용되는 SELECT문 = 스칼라 서브쿼리(Scala Subquery)
WHERE구에 사용되는 SELECT문 = 서브쿼리(Subquery)
02. 단일 행 서브쿼리와 다중 행 서브쿼리
반환하는 행 수가 한 개인 것과 여러 개인 것에 따라 단일 행 서브쿼리와 다중 행 서브쿼리로 분류됨
단일 행 서브쿼리는 단 하나의 행만을 반환하는 서브쿼리로 비교 연산자로 비교가 가능함
다중 행 서브쿼리는 여러 개의 행을 반환하는 것으로 IN, ANY, ALL, EXISTS를 이용해 비교가 가능함
03. 다중 행 서브쿼리
다중 행 비교 연산자
다중 행 비교 연산자
설명
IN(SUBQUERY)
- 메인 쿼리의 비교조건이 Subquery의 결과 중 하나만 동일해도 참 (OR)
ALL(SUBQUERY)
- 메인 쿼리와 서브 쿼리의 결과가 모두 동일하면 참
ANY(SUBQUERY)
- 메인 쿼리의 비교조건이 서브 쿼리의 결과 중 하나 이상 동일하면 참
EXISTS(SUBQUERY)
- 메인 쿼리와 서브 쿼리의 결과가 하나라도 존재하면 참
SELECT * FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE SAL >= 5000);
04. 스칼라 서브쿼리
반드시 한 행과 한 칼럼만 반환하는 서브쿼리
단일 행 서브쿼리와 다른 점은 칼럼의 개수가 반드시 하나라는 것, 단일 행 서브쿼리는 칼럼의 개수가 하나일 필요는 없다
모든 스칼라 서브쿼리는 단일 행 서브쿼리이나, 모든 단일 행 서브쿼리가 스칼라 서브쿼리는 아님
SELECT * FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);
급여 평균보다 많이 받는 행 출력
05. 연관 서브쿼리
연관 서브쿼리는 서브쿼리 내에서 메인 쿼리 내의 칼럼을 사용하는 것
SELECT * FROM EMP A
WHERE A.EMPNO =
(SELECT EMPNO FROM EMP B
WHERE B.EMPNO = A.EMPNO);
