SQL기초6 - 서브쿼리
이번 SQL기초6에서는 서브쿼리에 대하여 학습해보려고 한다.
서브쿼리란, 메인쿼리 안에 있는 쿼리를 말한다.
<동작방식에 따른 서브쿼리 분류>
서브쿼리는 동작하는 방식에 따라 연관 서브쿼리와 비연관 서브쿼리로 나뉜다.
서브쿼리에 메인쿼리의 컬럼이 포함된다면 연관 서브쿼리, 아니라면 비연관 서브쿼리이다.
- 연관 서브쿼리: 메인쿼리의 컬럼이 서브쿼리에 포함되며, 메인쿼리의 컬럼은 서브쿼리에 특정 조건으로 사용된다.
예) 본인이 속한 부서의 평균 급여보다 높은 급여를 받는 직원들 출력
SELECT id, department_id, name, salary FROM EMPLOYEE A
WHERE salary> (SELECT AVG(salary) FROM EMPLOYEE B WHERE B.department_id = A.department_id);위의 코드를 보면 알 수 있듯이, 메인쿼리의 WHERE절에 평균급여를 추출하는 서브쿼리가 포함되어있다.
서브쿼리에서 뽑은 평균급여는메인쿼리의 특정 조건으로 사용되므로 서로 연관이 있다.
-비연관 서브쿼리: 메인쿼리 컬럼이 서브쿼리에 포함되지 않으며, 주로 메인 쿼리에 특정한 값을 제공할 때 사용된다.
예) ELICE가 속한 부서의 평균 급여 출력
SELECT AVG(salary) FROM EMPLOYEE
WHERE department_id = (SELECT department_id FROM EMPLOYEE WHERE name = 'ELICE');메인쿼리에서는 급여 컬럼을 추출하는데 서브쿼리에서는 부서번호를 추출하므로 아무런 연관이 없다는 것을 알 수 있다.
<반환되는 데이터 형태에 따른 서브쿼리 분류>
쿼리의 추출 결과가 단일 행인지, 다중 행인지, 다중 컬럼인지에 따라 서브쿼리가 분류된다.
- 단일 행 서브쿼리: 서브쿼리의 결과가 한 개의 행만을 반환하며, 단일 행 비교연산자인 =, <. >, <=, >= 가 같이 사용된다.
예) ELICE가 속한 부서의 직원들 출력
SELECT id, name, salary FROM EMPLOYEE
WHERE department_id = (SELECT department_id FROM EMPLOYEE WHERE name = 'ELICE');메인쿼리의 WHERE절에 서브쿼리가 존재하며, 추출 값이 단일 행이므로, 단일 행 비교연산자인 =을 사용하고 있다.
- 다중 행 서브쿼리: 서브쿼리의 결과가 두 개 이상의 행을 반환할 수 있으며, 다중 행 비교연산자인 IN, ALL, ANY, EXISTS가 함께 사용된다
<다중 행 비교연산자의 정의>
각각의 예제를 살펴보자.
1. IN - 모두 만족할 필요 x, 나온 값들 중 하나만 일치하면 됨
예) 영업 또는 개발 팀에 속하는 직원들 출력
SELECT name FROM EMPLOYEE
WHERE department_id IN (SELECT id FROM DEPARTMENT WHERE name='품질' or name = '영업');employee 테이블의 department_id가 department테이블의 부서 이름이 품질이나 영업인 아이디와 일치하면 정보 출력
2. EXISTS - 무엇을 추출하던 간에 단순히 존재 여부만 확인하는 것 => 추출되냐 안되냐만 확인하면 됨
예) 급여가 10000을 넘는 직원이 존재하는 부서에 소속된 모든 직원들을 출력
SELECT name FROM EMPLOYEE A
WHERE EXISTS (SELECT id FROM EMPLOYEE B WHERE B.salary >= 10000 AND A.department_id = B.department_id);존재 여부만 알면 되므로 서브쿼리에서 id를 추출하지 않고 그저 0만 써도 정상적으로 동작한다.
3. ALL - 모두 만족
예) 개발 팀 소속 모든 직원들 급여보다 급여가 큰 직원들을 출력
SELECT name FROM EMPLOYEE
WHERE salary >= ALL(SELECT salary FROM employee WHERE department_id=1);서브쿼리에서 추출한 모든 급여들보다 더 많은 급여를 받는 사원들을 추출한다.
4. ANY - 하나 이상 조건을 만족하면 됨 => IN과 차이가 헷갈린다..
내 생각으로는 IN는 하나 이상의 값을 만족하면 되고, ANY는 조건이 하나 이상의 조건을 만족하면 되는 것 같다.
예) 개발 팀 소속 임의의 직원들 급여보다 급여가 더 큰 직원들을 출력
SELECT name FROM EMPLOYEE
WHERE salary >= ANY(SELECT salary FROM EMPLOYEE WHERE department_id=1);
- 다중 컬럼 서브쿼리: 서브쿼리의 결과가 여러 개의 컬럼을 반환하며, 메인쿼리의 조건과 동시에 비교된다.
예) 각 부서에서 가장 높은 급여를 받는 직원의 이름과 급여를 출력
SELECT name, salary FROM EMPLOYEE
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)
FROM EMPLOYEE GROUP BY department_id);서브쿼리의 결과가 다중 컬럼이므로, 메인쿼리의 where절에서 여러 개의 컬럼과 비교해줘야한다.
==> 반드시 ( ) 괄호 쓸 것!
<스칼라 서브쿼리>
스칼라 서브쿼리란, 하나의 속성을 가지면서 하나의 행만을 반환하는 쿼리이다.
==> SELECT, WHERE, HAVING 절 등에서 사용할 수 있음
예) 부서명과 부서의 구성원 수 출력
SELECT name, (SELECT COUNT(*) FROM EMPLOYEE E WHERE E.department_id = D.id) FROM DEPARTMENT D;+) 특정 테이블의 참조 없이 쿼리를 작성하는 경우도 있다.
==> 뭐 예를 들어.. SELECT 10-5를 실행하면 5가 나온다. 굳이 FROM 테이블명을 안적어도 됨
또는 ~~FROM DUAL;을 쓴다거나...==> 여기서 DUAL 테이블은 실제 존재하는 테이블이 아니라, 형식을 맞춰주기 위해 적은 테이블이다.
<VIEW>
VIEW: 다른 테이블에서 파생된 테이블/ 물리적으로 데이터가 저장되는 것이 아니라, 논리적으로만 존재한다.
VIEW의 장점
VIEW의 특징
문법)
CREATE VIEW 뷰이름 AS (뷰를 생성할 쿼리 구문: SELECT~);만약에 같은 이름의 뷰가 존재하면 기존 뷰를 무시하고 새로 만든 뷰로 대체한다는 키워드 => REPLACE
즉, CREATE 대신 REPLACE를 쓰면 된다.
REPLACE VIEW 뷰이름 AS (뷰를 생성할 쿼리 구문: SELECT~ );
예시)
매 번 조인이 불편하다면 뷰 테이블을 따로 생성하여 활용하면 편리하다.