SQL - IN, EXITS, ANY, ALL

*IN

‘=’비교만 가능하고, OR 조건 검색 결과를 가져온다.

서브쿼리가 먼저 실행되고 그 결과를 바탕으로 메인쿼리가 실행된다.

 

*EXISTS

메인쿼리의 비교 조건이 서브쿼리의 결과 중에 만족하는 값이 있다면 1, 없다면 0

메인쿼리를 먼저 실행한다 -> 메인쿼리로 범위를 좁히고 서브쿼리로 값을 찾는다.

공통된 컬럼이 없는 경우 exists하면 1과 0에 따라 값을 다 보여주거나 다 보여주지 않는다.

등가조인을 사용할 경우(공통된 컬럼이 있을 경우)에는 exists 비교를 통해 조건에 맞는 데이터면 1이어서 보여주고, 조건을 만족하지 않으면 0이기 때문에 보여주지 않는다.

exists는 in보다 성능이 더 좋다 -> 메인쿼리로 범위를 좁혀놓고 시작하기 때문에

 

*ANY

메인쿼리와 비교조건이 서브쿼리 결과 중 하나라도 일치하면 참이다.

1) = any : in과 같은 효과

2) > any : 최솟값보다 더 크면

3) < any : 최댓값보다 더 작으면

 

*ALL

ANY와 반대 개념

1) = all : any와 같지만 or이 아니라 and 연산이다. -> 값이 하나인 것과 값이 여러 개 인 것을 비교하는 것 자체가 잘못되었으므로 무조건 값이 하나가 나오는 쿼리여야 한다.

2) < all : 최솟값보다 더 작으면

3) > all : 최댓값보다 더 크면

 

=> ANY와 ALL은 MAX, MIN과 성능 차이가 없기 때문에 아무거나 사용해도 된다.

 

-- 1. IN : = 비교만 가능, OR 조건 검색 결과 내용을 가져온다.
-- 서브쿼리가 먼저 실행되고, 그 결과를 바탕으로 메인쿼리가 동작한다.
select ename, job from emp where deptno in (2,4);
select ename, job from emp where deptno in (select deptno from dept where loc in ('la','boston'));

-- 2. EXISTS : 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에 만족하는 값이 하나라도 존재하면 1 혹은 0 반환
-- 메인쿼리로 범위를 좁히고 서브쿼리로 값을 찾는다.
-- exists는 결과값이 있으면 1, 없으면 0을 반환
select exists (select deptno from dept where loc in ('la','boston')) as bool;

select ename, job, deptno from emp where exists (select deptno from dept where loc in ('la','boston'));
select ename, job, deptno from emp where 1;

select ename, job, deptno from emp where exists (select deptno from dept where loc in ('korea'));
select ename, job, deptno from emp where 0;

-- 그럼 어떻게 사용하는가?
-- 1) 메인쿼리가 실행된다.
select ename, job, deptno from emp;
-- 2) 이 메인쿼리 내용을 서브쿼리에서 걸러준다. (등가조인처럼 양쪽 테이블에 모두 있는 내용을 가져온다.) -> 0이면 값을 안 보여주기 때문에
select ename, job, deptno from emp e where exists (select deptno from dept d where e.deptno = d.deptno);
-- 결과적으로 왜 이렇게 나오나?
-- 공통되는 컬럼이 있을 경우 행마다의 exists 비교를 통해 보여주고 안 보여주고 하는 것이다. -> emp의 deptno와 dept의 deptno가 같은지 계속 비교를 하기 때문에

-- in 과의 사용법 비교
select ename, job from emp where deptno in (select deptno from dept where loc in ('la','boston'));

select ename, job from emp e where exists(select deptno from dept d where d.deptno = e.deptno and (d.loc in ('la','boston')));
-- exists는 in보다 사용이 복잡하지만 성능이 더 좋다.
-- in은 서브쿼리를 통해 조건을 추출하고 메인쿼리의 내용을 검색한다.
-- exists는 메인쿼리를 통해 데이터를 확정하고 그 안에서 서브쿼리 조건을 걸러낸다.
-- 단, exists는 메인쿼리에서 얼마나 데이터를 좁혀놓고 시작하는지가 관건이다.

-- 3. ANY : 메인쿼리와 비교조건이 서브쿼리 결과 중에 하나라도 일치하면 참이다.
-- 1) = ANY : IN과 같은 효과
select ename, job from emp where deptno in (select deptno from dept where loc in ('la','boston'));

select ename, job from emp where deptno = any (select deptno from dept where loc in ('la','boston'));
 
-- 2) > ANY : 최소값보다 크면
-- 직책이 manager인 직원(입사일이 가장 빠른)의 입사일보다 늦은 직원의 이름과 직책과 입사일
-- 1. 직책이 manager인 입사일이 가장 빠른 직원
select MIN(hiredate) from emp where job = 'manager';
-- 2. 입사일이 2015-08-12 보다 늦은 직원 찾기
select ename, job, hiredate from emp where hiredate > '2015-08-12';

select ename, job, hiredate from emp where hiredate > (select MIN(hiredate) from emp where job = 'manager');

select ename, job, hiredate from emp where hiredate > any (select hiredate from emp where job = 'manager');

-- 3) < ANY : 최대값보다 작으면
-- 직책이 manager인 직원(입사일이 가장 늦은)의 입사일보다 빠른 직원의 이름과 직책과 입사일
select ename, job, hiredate from emp where hiredate < (select MAX(hiredate) from emp where job = 'manager');

select ename, job, hiredate from emp where hiredate < any (select hiredate from emp where job = 'manager');

-- 4. ALL : ANY와 반대 개념
-- 1) = ALL : ANY와 같지만 OR 조건이 아닌 AND 조건이다.
select ename, job from emp where deptno = all(select deptno from dept where loc = 'newyork');

select deptno from dept where loc = 'newyork'; -- 1,3
-- = all 안의 조건은 and 이므로 deptno=1 AND deptno=3 이므로 맞는 조건이 없다.
-- 그래서 결과값이 하나만 나오는 조건으로 사용해야 한다.
select ename, job from emp where deptno = all(select deptno from dept where loc = 'newyork' and deptname = 'sales');

-- 2) > ALL : 최대값보다 크면
select ename, job, hiredate from emp where hiredate > (select MAX(hiredate) from emp where job = 'manager');

select ename, job, hiredate from emp where hiredate > all (select hiredate from emp where job = 'manager');

-- 3) < ALL : 최소값보다 작으면
select ename, job, hiredate from emp where hiredate < (select MIN(hiredate) from emp where job = 'manager');

select ename, job, hiredate from emp where hiredate < all (select hiredate from emp where job = 'manager');