[MySQL] 6. 비교연산자(IN, ANY, ALL)문제

1. IN연산자

 

MySQL에선 AND를 BETWEEN로 써도 되는 것처럼 OR는 IN으로 써도 된다.

 

예를 들어 급여가 1000~1500 사이의 직원들을 보려고 할 때 between으로 간단하게 쓸 수 있다

 

and(between)

 

select * from employee where salary >= 1000 and salary <= 1500;
=>select * from employee where salary between 1000 and 1500;

 

 

직업이 영업사원이거나 사원인 직원들

or(in)

 

select * from employee where job = 'salesman' or job = 'clerk';
=>select * from employee where job in ('salesman','clerk');

 

 

 

문제) 부서별 최소 급여를 받는 사원의 사원번호와 이름, 급여를 출력하시오.

 

select dno, ename, salary from employee where salary = (select min(salary) from employee group by dno);
-- 에러

 

에러나는 이유는 where salary가 받는 서브쿼리값이 group by dno, 3개이기 때문이다.

 

= 연산자는 하나일 때만 적용된다. 여러개를 받을 땐 in 을 써야한다.

 

select dno, ename, salary from employee where salary in (select min(salary) from employee group by dno);

 

---

2. ANY연산자

A > any B : 최소값보다 큼 (아무런 B가 와도 A가 크다. B의 모든 내용 고려해서 한번이라도 A가 크면 된다)

A < any B : 최대값보다 작음 (아무런 B보다도 A가 작다. B의 모든 내용 고려해서 한번이라도 A가 크면 된다)

 

 

문제) 담당업무가 salesman이 아니면서 급여가 임의의 salesman보다 낮은 사원들을 출력하시오.

 

임의의 salesman보다 낮다는건 salesman의 최대 salary보다 작아도 된다는 의미이다.

 

참고로 임의의 salesman의 salary는

select salary from employee where job='salesman'

 

서브쿼리로 한번에 작성하면

select eno, ename, job, salary from employee
where salary < any (select salary from employee where job='salesman') and job<>'salesman';

 

 

1600보다 작은 값 모두 출력

 

1600보다 작은 사람이 모두 출력된다.

 

자주하는 실수 : where 하고 연산자 바로 쓰는거 조심 ( where <    에러)

where 컬럼명 연산자 값

 

 

최대값보다 작다는 조건이기 때문에 꼭 any를 안쓰고 max()를 이용해도 결과는 똑같다

select eno, ename, job, salary from employee
where salary < (select max(salary) from employee where job='salesman') and job<>'salesman';

---

3. ALL연산자

A > all B : 최대값보다 큼 (어떤 B가 와도 A가 크다. B의 모든 내용 고려해도 A가 커야한다)

A < all B : 최소값보다 작음(어떤 B보다도 A가 작다. B의 모든 내용 고려해도 A가 작아야한다)

 

 

위 문제에서 any를 all로 바꾸면 salesman의 가장 낮은 salary 보다도 작아야하는 값들이 출력된다.

select eno, ename, job, salary from employee
where salary < all (select salary from employee where job='salesman') and job<>'salesman';

1250보다 작은 것만 출력

 

 

---

 

문제) 사원번호가 7788인 사원과 담당업무가 같은 사원을 조회하시오.(이름, 담당업무)

select ename, job from employee where job = (select job from employee where eno = 7788);

 

 

 

문제) 사원번호가 7499인 사원보다 급여를 많이 받는 사원을 조회하시오,(이름, 담당업무)

select ename, job from employee where salary > (select salary from employee where eno = 7499);

 

 

문제) 최소 급여를 받는 사원의 이름, 담당업무, 급여를 조회하시오.

select ename, job, salary from employee where salary = (select min(salary) from employee);

 

 

문제) 각 부서별 최소 급여를 받는 사원의 이름, 업무, 급여, 부서번호를 조회하시오.

 

부서별 최소급여이므로 group by를 써야한다.
서브쿼리의 값이 한개가 아니므로 =이 아닌 in을 써야함.

select ename, job, salary, dno from employee
where salary in (select min(salary) from employee group by dno);

 

 

문제) BLAKE와 동일한 부서에서 근무하는 사원의 이름과 입사일을 조회하시오.

 

서브쿼리는 값이 안주어질 때 쓰면된다.
만약 blake부서가 30이라고 주어지면 그냥 select ename, hiredate from employee where dno=30 하면된다.

select ename, hiredate from employee where dno = (select dno from employee where ename = 'blake');

 

 

BLAKE를 제외시키고 싶을 때

select ename, hiredate from employee
where dno = (select dno from employee where ename = 'blake') and ename<>'blake';

---

난이도up?

문제) 급여가 평균 급여보다 많은 사원들의 사원번호, 이름, 급여를 표시하되 급여에 대해서 오름차순으로 정렬하시오

select eno, ename, salary from employee
where salary > (select avg(salary) from employee) order by salary; -- asc생략

 

 

문제) 부서 위치가 DALLAS인 사원의 이름, 부서번호, 담당업무를 표시하시오

select ename, dno, job from employee where dno = (select dno from department where loc='DALLAS');

 

조인으로도 풀 수 있다

select ename, a.dno, job from employee a inner join department b
on a.dno=b.dno where LOC='DALLAS';

 

문제) 평균 급여가 가장 적은 업무를 찾으시오.(업무명, 평균급여 출력)

select job, avg(salary) from employee group by job; -- 업무별로 평균구함

 

업무별로 평균 급여를 구한 것에 조건을 주면 된다.

GROUP BY가 먼저 나왔기 때문에 조건은 WHERE가 아니라 HAVING으로 준다.

select job, avg(salary) from employee group by job
having avg(salary) <=all(select avg(salary) from employee group by job);