예를 들어, LEFT OUTER JOIN이라면 왼쪽 테이블의 모든 행을 기준으로 오른쪽 테이블을 JOIN합니다.
다음은 OUTER JOIN에 대한 쿼리입니다.
-- Oracle LEFT OUTER JOIN --
SELECT
e.last_name AS 사원명,
e.employee_id AS 사원번호,
m.last_name AS 관리자명,
m.employee_id AS 관리자사번
FROM
employees e,
employees m
WHERE
e.manager_id = m.employee_id (+);
-- Ansi LEFT OUTER JOIN --
SELECT
e.last_name AS 사원명,
e.employee_id AS 사원번호,
m.last_name AS 관리자명,
m.employee_id AS 관리자사번
FROM
employees e LEFT OUTER JOIN employees m -- OUTER 키워드 생략 가능 --
ON e.manager_id = m.employee_id;
RIGHT OUTER JOIN도 동일한 개념으로 동작합니다.
그런데 Ansi JOIN에서만 있는 FULL OUTER JOIN이 있습니다.FULL OUTER JOIN은 두 테이블의 모든 행을 결합하되, NULL값을 모두 포함하여 결합합니다.
-- Ansi FULL OUTER JOIN --
SELECT
e.last_name AS 사원명,
e.employee_id AS 사원번호,
m.last_name AS 관리자명,
m.employee_id AS 관리자사번
FROM
employees e FULL OUTER JOIN employees m -- OUTER 키워드 생략 가능 --
ON e.manager_id = m.employee_id;
GROUP BY(그룹화)는 SELECT 절에서 집계함수와 단순칼럼이 복합적으로 선택될 때 사용됩니다.
1. GROUP BY
GROUP BY절은 NULL을 제외하지 않고 그룹화하여 로우의 가장 아래에 생성합니다.
먼저 SELECT 절에 스칼라를 선택하는 쿼리를 작성하였습니다.
스칼라는 쿼리의 결과가 하나인 것을 의미합니다.
SELECT
MAX(salary)
FROM
employees;
스칼라
결과에서도 알 수 있듯이,
집계함수가 단독적으로 사용된다면 그룹화는 필요 없습니다.
하지만 집계함수와 단순칼럼이나 표현식이 함께 사용된다면 GROUP BY 절이 사용되야 합니다.
고용된 날짜 별 총 급여를 쿼리로 작성하였습니다.
SELECT
TO_CHAR(hire_date, 'YYYY/MM') AS 고용연월,
SUM(salary) AS "총 급여"
FROM
employees
GROUP BY
TO_CHAR(hire_date, 'YYYY/MM')
ORDER BY
1 ASC;
위 쿼리는 TO_CHAR를 그룹화하여 해당 고용날짜에 해당하는 사원의 총 급여에 대한 쿼리입니다.
만약 SELECT절에 추가적인 칼럼이나 표현식이 선택되면 GROUP BY절에서 추가적인 그룹화가 필요합니다.
2. HAVING
HAVING절은 그룹화와 함께 사용됩니다.
HAVING절은 GROUP BY를 통해 그룹화하고 로우(row, 행)가 아닌 그룹을 제외시킬 수 있습니다.
또한 WHERE절에서는 사용이 불가능한 집계함수를 HAVING절에서는 사용할 수 있습니다.
SELECT
TO_CHAR(hire_date, 'YYYY/MM') AS 고용연월,
SUM(salary) AS "총 급여"
FROM
employees
GROUP BY
TO_CHAR(hire_date, 'YYYY/MM')
HAVING
SUM(salary) >= 10000
ORDER BY
1 ASC;
위 쿼리처럼 HAVING절은 집계함수 사용이 가능하며, WHERE절과 같은 그룹에 대한 필터링을 수행합니다.
인라인 뷰는 기존 테이블에서 필요한 칼럼만 추출 후 일종의 임시 테이블로 사용하는 방식입니다.
인라인 뷰를 사용하면 쿼리가 더 간결해지고 중복 코드를 제거할 수 있습니다.
다만, 과도하게 중첩된 인라인 뷰는 쿼리의 복잡도를 높일 수 있기 때문에 주의해야 합니다.
간단한 예시 코드입니다.
SELECT
부서번호,
평균월급
FROM
( SELECT
department_id AS 부서번호,
AVG(salary) AS 평균월급
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY
department_id
ORDER BY
department_id ASC;
);
SELECT
table_name,
constraint_type,
constraint_name,
r_constraint_name
FROM
user_constraints
WHERE
table_name IN ('DEPT02', 'EMP02')
ORDER BY
table_name ASC;
FORGEIGN KEY
위의 결과에서, emp02 테이블의 참조키는 dept02 테이블의 기본키를 참조하는 것을 알 수 있습니다.
특정 칼럼을 참조하고 있는 자식이 있으면 해당 칼럼을 삭제할 수 없습니다.
만약 부모 테이블의 칼럼을 삭제하고 싶다면,
테이블 작성 시, FK에 ON DELETE CASCADE 키워드나 ON DELETE SET NULL 키워드를 사용하면 됩니다.
CREATE TABLE emp02 (
empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
ename VARCHAR(15),
deptno NUMBER(2)
CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE CASCADE
);
-- ON DELETE CASCADE --
-- 참조하는 부모 테이블의 로우가 삭제되면, 해당 로우를 참조하는 자식 테이블의 로우도 연쇄 삭제됨 --
CREATE TABLE emp03 (
empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
ename VARCHAR(15),
deptno NUMBER(2)
CONSTRAINT emp03_deptno_fk REFERENCES dept02(deptno) ON DELETE SET NULL
);
-- ON DELETE SET NULL --
-- 참조하는 부모 테이블의 로우가 삭제되면, 해당 로우를 참조하는 자식 테이블의 필드값은 NULL로 바뀜--
