JOIN 심화 + 서브쿼리 정리
Non-Equi Join (범위 조건 조인)
Non-Equi Join은 = 같은 동등 조건이 아니라, BETWEEN, >, < 같은 범위 조건으로 조인하는 방식이다.
-- salgrade 테이블이 있다고 가정
-- 급여 등급 조인
SELECT
e.ename,
e.sal,
s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;참고: 위 문법은 구식(쉼표 조인) 방식이다. 실무/가독성 측면에서는 ANSI JOIN 문법을 더 권장한다.
LEFT OUTER JOIN
왼쪽 테이블의 모든 행이 결과에 포함되고, 오른쪽이 매칭되지 않으면 오른쪽 컬럼은 NULL이 된다.
-- 부서가 없는 직원도 포함
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;LEFT OUTER JOIN은 LEFT JOIN으로 축약 가능하다.
-- LEFT JOIN으로 축약 가능
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;RIGHT OUTER JOIN
오른쪽 테이블의 모든 행이 결과에 포함되고, 왼쪽이 매칭되지 않으면 왼쪽 컬럼은 NULL이 된다.
-- 직원이 없는 부서도 포함
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;FULL OUTER JOIN
MySQL은 FULL OUTER JOIN을 직접 지원하지 않기 때문에 보통 UNION으로 구현한다.
-- 방법: LEFT JOIN + RIGHT JOIN + UNION
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 중복 제거를 위해 추가
UNION은 기본적으로 중복을 제거한다.
성능 때문에 중복 제거가 필요 없으면UNION ALL도 고려하지만, FULL OUTER JOIN 구현에서는 중복 처리 기준을 명확히 잡아야 한다.
SELF JOIN
자기 자신과 조인해서 같은 테이블에서 관계를 표현한다.
대표적으로 “사원 - 상사” 관계를 조회할 때 사용한다.
-- 직원과 상사 정보 조회
SELECT
e.employee_id AS 사원ID,
e.first_name AS 사원이름,
m.employee_id AS 상사ID,
m.first_name AS 상사이름
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;SELF LEFT OUTER JOIN
상사가 없는 사람(최고 경영자 등)도 포함하려면 LEFT JOIN을 사용한다.
-- 상사가 없는 사람(최고 경영자)도 포함
SELECT
e.employee_id AS 사원ID,
e.first_name AS 사원이름,
m.employee_id AS 상사ID,
m.first_name AS 상사이름
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;서브쿼리(SubQuery)
서브쿼리는 하나의 SQL 질의문 안에 다른 SQL 질의문이 포함된 형태다.
-- SCOTT의 급여보다 높은 급여를 받는 사람
SELECT ename
FROM emp
WHERE sal > (
SELECT sal
FROM emp
WHERE ename = 'SCOTT'
);Single-Row Subquery
서브쿼리 결과가 1행(단일 값) 인 경우다.
=, <, >, <=, >= 같은 연산자로 비교한다.
-- 평균 급여보다 적은 급여를 받는 사원
SELECT ename, sal
FROM emp
WHERE sal < (
SELECT AVG(sal)
FROM emp
);-- 가장 먼저 입사한 사원
SELECT ename, hiredate
FROM emp
WHERE hiredate = (
SELECT MIN(hiredate)
FROM emp
);Multi-Row Subquery
서브쿼리 결과가 여러 행인 경우다.
IN, ANY, ALL 같은 연산자를 사용한다.
IN 연산자
SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (
SELECT deptno
FROM dept
WHERE loc IN ('NEW YORK', 'DALLAS')
);ANY 연산자
sal > ANY (...) 는 “서브쿼리 결과 중 하나라도 만족하면 true”
즉, 최소값보다 크면 조건을 만족할 가능성이 높다(비교 연산에 따라 해석 달라짐).
SELECT ename, sal
FROM emp
WHERE sal > ANY (
SELECT sal
FROM emp
WHERE deptno = 30
);ALL 연산자
sal > ALL (...) 는 “서브쿼리 결과 전부보다 커야 true”
즉, 최대값보다 커야 만족한다.
SELECT ename, sal
FROM emp
WHERE sal > ALL (
SELECT sal
FROM emp
WHERE deptno = 30
);상관 서브쿼리(Correlated Subquery)
내부 쿼리가 외부 쿼리의 값을 참조해서, 행마다 서브쿼리가 수행되는 형태다.
-- 자신이 속한 부서의 평균 급여보다 많이 받는 사원
SELECT o.ename, o.sal, o.deptno
FROM emp o
WHERE o.sal > (
SELECT AVG(i.sal)
FROM emp i
WHERE i.deptno = o.deptno
);EXISTS 연산자
EXISTS는 “서브쿼리 결과가 존재하는지 여부만” 확인한다.
보통 조인 대신 존재 여부 판단에 강점이 있다.
부하직원이 있는 직원만 조회
SELECT e.employee_id, e.first_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM employees s
WHERE s.manager_id = e.employee_id
);부하직원이 없는 직원만 조회
SELECT e.employee_id, e.first_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees s
WHERE s.manager_id = e.employee_id
);