* 서브쿼리란?
- 쿼리구문 안에 또다시 쿼리구문 이들어가 있는 형태. 모르는 값을 기준으로 쿼리구문을 작성해야 하는 경우 사용됨.
- group by 절을 제외한 쿼리구문 전체에 사용 가능함.
- 서브쿼리 유형 : 단일행 서브쿼리, 다중행 서브쿼리
[ 문법 ]
select 컬럼1, 컬럼2, 컬럼3...
from 테이블명
[where 좌변 = ( select 컬럼.
from 테이블명
[where 조건문] ) ]
[group by 컬럼명]
[having 조건문]
[order by 컬럼명];
-- Q. employees 테이블에서 Abel보다 급여를 더 많이 받는 사원은??
select salary from employees where last_name = 'abel'; -- 아벨의 월급
select employee_id, last_name, salary, department_id from employees
where salary > 11000;-- (Abel의 급여);
select employee_id, last_name, salary, department_id from employees
where salary > ( select salary
from employees
where last_name = 'Abel');
-- Q. davies 보다 입사일이 늦은 사원들을 출력하시오.
select last_name, hire_date from employees
where hire_date > (select hire_date
from employees
where last_name='davies');
(1) 단일행 서브쿼리
- 서브쿼리로 부터 메인쿼리로 단일행(단일값)이 반환되는 유형
- 단일행 서브쿼리인 경우 메인쿼리에 단일행 비교 연산자 사용해야 함.
- 단일행 비교연산자 : =, >, >=, <,<=,<>,!=
-- Q. employee_id가 141번인 사원과 동일 업무 담당자를 출력하되, 141번은 제외하시오.
select last_name, job_id from employees
where job_id = (select job_id from employees where employee_id=141)
and employee_id <> 141;
-- Q. employees 테이블에서 최소 급여를 받는 사원을 출력하시오
select last_name, job_id, salary
from employees
where salary = (select min(salary) from employees); -- (O) 결과 : Olson, ST_CLERK, 2100
-- (!=)
select last_name, job_id, min(salary) from employees; -- (X) 결과 : King, AD_PRES, 2100 => 최소급여빼고는 전부 첫번째 인덱스만 나옴.
-- Q.Lee 와 같은 업무를 하는 사원중에 Lee보다 월급이 높은 사원을 출력하시오
select last_name, job_id, salary from employees
where job_id = (select job_id from employees where last_name = 'Lee')
and salary > (select salary from employees where last_name = 'Lee');
* having 절에 서브쿼리가 사용된 예제
-- 부서별 최소 급여를 출력하되,
-- 30번 부서의 최소 급여보다 큰 부서만 출력하시오.(부서가 없느 사원제외)
-- 내가 만든 것.
SELECT department_id, MIN(salary) FROM employees
WHERE department_id IS NOT NULL
AND salary > (SELECT salary FROM employees WHERE department_id = 30)
GROUP BY department_id;
-- 오류 : Error Code: 1242. Subquery returns more than 1 row ( 메인쿼리에 단일행 비교 연산자 = 을 썼는데 서브쿼리에 값이 다중행이 나와서 오류 메시지가 뜬다.)
-- 오류의 원인은? 단일행 비교연산자가 작성되어 있는데 서브쿼리로 부터 여러행이 반환되어 오류가 생김.
-- employees 테이블에서 department = 30 이 여러줄이 있음.
select department_id, MIN(salary) from employees
where department_id is not null
group by department_id
having MIN(SALARY) > (select min(SALARY) from employees where department_id = 30);
* 단일행 서브쿼리로 부터 null 값이 반환된 경우
- 오류는 나지않지만, 메인쿼리 결과도 null이다.
select last_name, job_id
from employees
where job_id = ( select job_id
from employees
where last_name = 'Haas');
-- Haas 라는 사람이 없는 것이다.Haas 라는 사람이 1명이라면 자기자신이라도 나와야 하니까!
-- 서브쿼리에 결과가 없으면 null이 메인쿼리로 넘어가므로 결과가 나오지 않는 것이다.
(2) 다중행 서브쿼리
- 서브쿼리로부터 메인쿼리로 다중행이 반환됨.
- 메인쿼리에는 우변에 값리스트가 올 수 있는 다중행 비교연산자 사용해야 함.
- 다중행 비교연산자 : in(=, or), not in(<>, and), any(or), all(and)
- any, all은 논리 연산자의 성격만 있고 비교 연산자의 성격은 없어서 하나만으로는 비교연산자로 사용할 수 없고 비교연산자와 조합하여 사용하여야 함.
- =any : (=, or) (==) in : (=, or)
- >any : (>, or) : 최소값보다 큰지를 비교함.
- >=any: (>=, or): 최소값보다 크거나 같은지를 비교함.
- <any : (<, or) : 최대값보다 작은지를 비교함. <ANY 연산을 할때는 최대값 하나를 찾는다. 서브쿼리에서 값리스트를 100개를 보내면 어떻게 다하노?그니까 최대값(9000 )을 하나 찾아서 하나만 만족하면 OK 한다.
- <=any :(<=, or) : 최대값보다 작거나 같은지를 비교함.
- <>any : (<>, or): 연산자로써의 성격이 없음, 사용안됨!
- =all : (=, and)
- >all : (>, and)
- >=all: (>=, and)
- <all : (<, and)
- <=all: (<=, and)
- <>all: (<>, and) (==) not in : (<>, and)
-- Q. 174, 141 매니저 아이디가 있고, 174, 141 부서아이디가 있고, 174, 141 의 사원아이디가 없는.
select employee_id, last_name, manager_id, department_id
from employees
where MANAGER_ID in (select MANAGER_ID from employees where EMPLOYEE_ID in (174,141))
and department_id in (select department_id from employees where EMPLOYEE_ID in (174,141))
and EMPLOYEE_ID not in (174,141);
* 다중행 비교연산자 ANY 사용예제
-- 업무가 IT_PROG 인 직원의 연봉보다 적고, 업무가 IT_PROG가 아닌 사원을 출력하시오.
select employee_id, last_name, job_id, salary
from employees
where salary < ANY (select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
'자바스프링웹공부(2024) > 마이에스큐엘' 카테고리의 다른 글
2024.08.29. 트랜잭션, 트랜잭션 제어어(TCL) (3) | 2024.09.01 |
---|---|
2024.08.28. 데이터조작어(DML) - insert, update, delete (2) | 2024.09.01 |
2024.08.13. 그룹함수, group by, having 절 (0) | 2024.08.13 |
2024.08.12. 단일행함수 - 2. - 변환함수, 제어흐름함수, 시스템정보함수. (0) | 2024.08.13 |
2024.08.07. 단일행 함수 - 문자, 숫자 (0) | 2024.08.09 |