Day - 35 [Maria DB] IN _ EXISTS _ ANY _ ALL

IN & EXISTS 

 

1. IN : '=' 비교만 가능, OR 조건의 검색 결과 내용을 가져온다.

• 서브쿼리가 먼저 동작 된 후 메인쿼리가 동작 된다.

select ename, job from emp e where deptno IN(2,4);

--서브쿼리 먼저 동작, 메인쿼리 동작
select ename, job from emp e where deptno
	IN (select deptno from dept where loc = 'LA' or loc = 'boston');

IN 결과 값 둘 다 같음

 

2. EXISTS : 메인쿼리 실행 후 서브쿼리 조건을 비교 한다.

• 해당 결과가 있는지 1과 0 을 반환

select exists (select deptno from dept where loc = 'LA' or loc = 'boston') as yn; -- 1


-- exists 의 결과 값이 1 이면 데이터를 보여준다.
select ename, job, deptno from emp where 1;
select ename, job, deptno from emp 
	where exists (select deptno from dept where loc = 'LA' or loc = 'boston');
    
-- exists 의 결과 값이 0 이면 데이터를 안보여준다.
select ename, job, deptno from emp where 0;
select ename, job, deptno from emp
	where exists (select deptno from dept where loc = 'korea');

 

• 그럼 어떻게 봐야 하는가? (둘의 상관관계를 이해 해야 한다.)
• 1) 메인 쿼리가 먼저 실행

-- 1) 메인 쿼리 먼저 실행
select ename, job, deptno from emp;

-- 2) 가져온 데이터를 하나하나 조건에 맞는지 여부를 따진다.
-- dept 와 emp 의 공통되는 칼럼이 있으므로 이 둘을 하나하나 맞춰가는 것이다.
select ename, job, deptno from emp e
	where exists(select deptno from dept d where e.deptno = d.deptno);
-- 3) 결과적으로 확보한 데이터에 loc='LA' or loc='boston' 조건을 추가 해준다.
select ename, job, deptno from emp e
	where exists(select deptno from dept d where e.deptno = d.deptno 
	and (loc = 'LA' or loc = 'boston'));

 

• EXISTS 가 IN 보다 사용은 복잡하지만 속도면에서 우월하다.
• IN 은 조건을 추출하고 그 조건을 전체 데이터를 검색하지만
• EXISTS 는 메인쿼리를 통해 데이터를 확보해놓고, 서브쿼리의 조건으로 걸러내기 때문이다.

 

---

 

ANY & ALL

• ANY : IN 과 같은 효과 + 부등호도 사용 가능 (=,<,>)

select ename, job from emp e where deptno
	IN (select deptno from dept where loc = 'LA' or loc = 'boston');
    
select ename, job from emp e where deptno
	= ANY (select deptno from dept where loc = 'LA' or loc = 'boston');

같은 결과 값 ANY

-- > ANY : 최소값 보다 크면 (MIN 필요 없어짐)
-- 직책이 MANAGER 인 직원의 입사일이 가장 빠른 사람보다 늦은 이름과 직책 입사일

select ename, job, hiredate from emp
	where hiredate > ANY(select hiredate from emp where job = 'manager')
    order by hiredate;
    

-- < ANY : 최대값보다 작으면
-- 직책이 MANAGER 인 직원의 입사일이 가장 늦은 사람보다 빠른 이름과 직책 입사일

select ename, job, hiredate from emp
	where hiredate < ANY(select hiredate from emp where job = 'manager')
    order by hiredate;

> ANY 최소값보다 크면

< ANY 최대값 보다 작으면

 

ALL : any 와 비슷하다. (부등호를 쓸 수 있다는 점에서...)

-- 1) = ALL : AND 조건을 사용한다.
-- (조건의 값이 복수개로 오면 사용 할 수 없다) => 안에 있는 조건이 단일조건에만 사용 해야 한다.
-- deptno 이 1 and 3 인데 한 사람이 둘 다 만족 할 수 없으니까 아무것도 안뜬다. (좀 어려움 논리)

select ename, job from emp where deptno = all(select deptno from dept where loc = 'newyork');
select * from dept;

설명) loc 가 newyork 인 사람은 1번 3번 2명인데 , 조건 값이 복수개로 오면 사용 할 수 없으므로 아무것도 안뜨게 되는것이다. **

 

-- 2) > ALL : 최대값 보다 크면
-- 직책이 MANAGER 인 직원의 입사일이 가장 늦은 사람 보다 더 늦은 이름과, 직책, 입사일

select ename, job, hiredate from emp
	where hiredate > ALL (select hiredate from emp where job = 'manager')
order by hiredate; 

-- han 2016-03-02


-- 3) < ALL : 최솟값 보다 작으면
-- 직책이 MANAGER 인 직원의 입사일이 가장 빠른 사람 보다 더 빠른 이름과, 직책, 입사일

select ename, job, hiredate from emp
	where hiredate < ALL (select hiredate from emp where job = 'manager')
order by hiredate; 

-- nam 2010-01-02 , cha 2012-03-02