Data Base/Oracle SQL

23.10.13. Oracle SQL IN, ANY, ALL

잇꼬 2023. 10. 13. 18:23
728x90
반응형
SMALL

■ 여러행 서브쿼리
    a. 서브쿼리 결과가 여러개의 값이 나오는 서브쿼리
    b. 여러행 비교연산자(IN, ANY, ALL)

SELECT * 
FROM hr.employees
WHERE salary IN ( 
                SELECT min(salary)
                FROM hr.employees
                GROUP BY department_id
                );

SELECT * 
FROM hr.employees
WHERE salary > ( --오류발생: 단일행 비교연산자
                SELECT salary
                FROM hr.employees
                WHERE job_id = 'IT_PROG'
                );


★1. ANY
    a. ANY 의 의미 OR 범주를 가지고 있다.

    b. (단일 비교연산자) ANY

 

1. > ANY : '최소값보다 크다' 의미

SELECT * 
FROM hr.employees
WHERE salary > ANY ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );

SELECT * 
FROM hr.employees
WHERE salary > 4200
OR salary > 4800
OR salary > 6000
OR salary > 9000;

SELECT * 
FROM hr.employees
WHERE salary > ( SELECT MIN(salary)
                 FROM hr.employees
                 WHERE job_id = 'IT_PROG' );

2. < ANY : '최대값보다 작다' 의미 

SELECT * 
FROM hr.employees
WHERE salary < ANY ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );
가정했을때,                 
salary = (4200, 4800, 6000, 9000)                

SELECT * 
FROM hr.employees
WHERE salary < 4200
OR salary < 4800
OR salary < 6000
OR salary < 9000;

SELECT * 
FROM hr.employees
WHERE salary < ( SELECT MAX(salary)
                 FROM hr.employees
                 WHERE job_id = 'IT_PROG' );

3. = ANY : IN 연산자와 동일. 

SELECT * 
FROM hr.employees
WHERE salary > ALL ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );

SELECT * 
FROM hr.employees
WHERE salary = ANY ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );
가정했을때,                 
salary = (4200, 4800, 6000, 9000)                

SELECT * 
FROM hr.employees
WHERE salary = 4200
OR salary = 4800
OR salary = 6000
OR salary = 9000;

SELECT * 
FROM hr.employees
WHERE salary IN ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );

 

★2. ALL
    a. ALL 의 의미 AND 범주를 가지고 있다.
    b. (단일 비교연산자) ALL

1. > ALL : '최대값보다 크다' 의미 

SELECT * 
FROM hr.employees
WHERE salary > ALL ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );
SELECT * 
FROM hr.employees
WHERE salary > 4200
AND salary > 4800
AND salary > 6000
AND salary > 9000;

 

2. < ALL : '최소값보다 작다' 의미 

SELECT * 
FROM hr.employees
WHERE salary > ( SELECT MAX(salary)
                 FROM hr.employees
                 WHERE job_id = 'IT_PROG' );

SELECT * 
FROM hr.employees
WHERE salary < ALL ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );
가정했을때,                 
salary = (4200, 4800, 6000, 9000)                

SELECT * 
FROM hr.employees
WHERE salary < 4200
AND salary < 4800
AND salary < 6000
AND salary < 9000;


3. = ALL : 논리적으로 의미가 없다. 

SELECT * 
FROM hr.employees
WHERE salary < ( SELECT MIN(salary)
                 FROM hr.employees
                 WHERE job_id = 'IT_PROG' );

SELECT * 
FROM hr.employees
WHERE salary = ALL ( SELECT salary
                     FROM hr.employees
                     WHERE job_id = 'IT_PROG' );         

SELECT * 
FROM hr.employees
WHERE salary = 4200
AND salary = 4800
AND salary = 6000
AND salary = 9000;

 

OR 진리표
TRUE OR TRUE TRUE
TRUE OR FALSE TRUE
TRUE OR NULL ( TRUE / FALSE ) ' TRUE '
FALSE OR NULL ( TRUE / FALSE ) NULL
AND 진리표
TRUE AND TRUE TRUE 
TRUE AND FALSE FALSE
TRUE AND NULL  ( TRUE / FALSE ) NULL
FALSE AND NULL  ( TRUE / FALSE ) ' FALSE  '


[문제1] 관리자(직속상관) 사원들의 정보를 출력해주세요. 

SELECT *
FROM hr.employees 
WHERE employee_id IN ( SELECT manager_id 
                       FROM hr.employees );



[문제2] 관리자(직속상관)가 아닌 사원들의 정보를 출력해주세요. 

SELECT *
FROM hr.employees 
WHERE employee_id NOT IN ( 
                           SELECT manager_id -- NULL 값이 있으면 조회가 불가
                           FROM hr.employees );
                           
SELECT * 
FROM hr.employees
WHERE employee_id != NULL 
AND employee_id != 100
AND employee_id != 101 ;


NOT IN 연산자를 사용시 서브쿼리를 NULL 값이 있으면 조회가 안된다
왜? TRUE AND NULL = NULL 이기 때문에 

SELECT *
FROM hr.employees 
WHERE employee_id NOT IN ( SELECT manager_id 
                           FROM hr.employees 
                           WHERE manager_id IS NOT NULL);

SELECT *
FROM hr.employees 
WHERE employee_id <> ALL ( SELECT manager_id 
                           FROM hr.employees 
                           WHERE manager_id IS NOT NULL);


자신의 부서 평균 급여보다 더 많이 받는 사원을 출력하세요. 

SELECT *
FROM hr.employees
WHERE salary > 자신의 부서 평균;

SELECT avg(salary) 
FROM hr.employees
WHERE department_id = 자신의 부서 코드;



■ 상호관련 서브쿼리(correlated subquery), 상관 서브쿼리 ex)존재여부 테스트 
    a. main query(outer query) 먼저 수행
    b. 첫 번째 행을 후보행으로 잡고 후보행 값을 서브쿼리에 전달
    c. 후보행 값을 사용해서 서브쿼리를 수행한다.
    d. 서브쿼리 결과값을 사용해서 후보행과 비교해서 true 이면 그 행을 결과집합(메모리)에 저장, false 면 넘어간다.
    e. 다음 행을 후보행으로 잡고 서브쿼리에 전달 하고 table(main query) row 의 수 만큼 서브쿼리문을 반복 수행.
     

SELECT * 
FROM hr.employees o --먼저 수행
WHERE salary > ( SELECT AVG(salary) -- 단점: '비효율적'으로 쿼리문을 반복 수행해야 한다.
                 FROM hr.employees
                 WHERE department_id = o.department_id );
                                    /* 미지수(변수), 후보행 값 */


# 상호관련 서브쿼리의 문제점
    a. 똑같은 후보행 값이 입력되더라도 무조건 서브쿼리는 수행해야 한다. 

SELECT department_id, avg(salary)
FROM hr.employees
GROUP BY department_id;


■ INLINE VIEW
    a. 가상 테이블 
    b. FROM 절에 괄호 안에 SELECT 문(서브쿼리)를 INLINE VIEW 라고 한다.
    c. INLINE 사용시에는 컬럼 및 테이블을 별칭을 써줘야 헷갈리지 않는다. 

SELECT o.dept_id, o.avg_sal, e.*
FROM ( SELECT department_id AS dept_id, round(avg(salary)) AS avg_sal
       FROM hr.employees
       GROUP BY department_id ) 
       o, hr.employees e 
WHERE o.dept_id = e.department_id
AND e.salary > o.avg_sal ;


■ EXISTS 연산자(존재여부 연산자, IN 과 비슷하나 성능은 더 좋다)
    a. 상호관련 서브쿼리에서 사용하는 연산자
    b. 후보행값이 서브쿼리에 존재하는지 여부를 찾는 연산자
    c. 후보행 값이 서브쿼리에 존재하면 TRUE. (찾는 데이터 검색종료) 후보행 값을 별도의 메모리 저장.
    d. 후보행 값이 서크뭐리에 존재하지 않으면, false. 찾는 데이터가 아님. 

SELECT *
FROM hr.employees e
WHERE EXISTS( SELECT 'X'
              FROM hr.employees
              WHERE manager_id = e.employee_id );
                /*      1쪽          M쪽      */
SELECT *
FROM hr.employees 
WHERE employee_id IN ( SELECT manager_id
                        FROM hr.employees );


■ NOT EXISTS 연산자
    a. 후보행 값이 서브쿼리에 존재하지 않으면 데이터를 찾는 연산자
    b. 후보행 값이 서브쿼리에 존재하지 않으면, TRUE. 찾는 데이터는 별도의 메모리에 저장.
    c. 후보행 값이 서브쿼리에 존재하면, FALSE. 찾는 데이터가 아니면 패스. 

SELECT *
FROM hr.employees e
WHERE NOT EXISTS( SELECT 'X'
                  FROM hr.employees
                  WHERE manager_id = e.employee_id );

SELECT *
FROM hr.employees 
WHERE employee_id NOT IN ( SELECT manager_id
                           FROM hr.employees
                           WHERE manager_id IS NOT NULL );


[문제1] 소속사원이 있는 부서정보(departments)를 출력해주세요
1) IN 연산자 

SELECT *
FROM hr.departments
WHERE department_id IN ( SELECT department_id
                         FROM hr.employees );


2) EXISTS 

SELECT *
FROM hr.departments d
WHERE EXISTS ( SELECT 'X'
               FROM hr.employees 
               WHERE department_id = d.department_id);


[문제2] 소속사원이 없는 부서정보(departments)를 출력해주세요
1) NOT IN 연산자 

SELECT *
FROM hr.departments d
WHERE department_id NOT IN ( SELECT department_id
                             FROM hr.employees 
                             WHERE department_id IS NOT NULL);


2) NOT EXISTS 

SELECT *
FROM hr.departments d
WHERE NOT EXISTS ( SELECT 'X'
                   FROM hr.employees 
                   WHERE department_id = d.department_id);
728x90
반응형
LIST
저작자표시 비영리 동일조건

'Data Base > Oracle SQL' 카테고리의 다른 글

23.10.17. Oracle SQL 집합연산자 UNION(합집합), UNION ALL(합집합), INTERSECT(교집합), MINUS(차집합)  (0) 2023.10.17
23.10.16. Oracle SQL PIVOT, UNPIVOT, 다중열 서브쿼리(비쌍비교 VS 쌍비교), SCALAR SUBQUERY(스칼라 서브쿼리)  (1) 2023.10.16
23.10.13. Oracle SQL SUBQUERY(서브쿼리)  (0) 2023.10.13
23.10.12. Oracle SQL JOIN ②  (0) 2023.10.12
23.10.12. Oracle SQL JOIN ①  (0) 2023.10.12

'Data Base/Oracle SQL'의 다른글

  • 현재글23.10.13. Oracle SQL IN, ANY, ALL

관련글

티스토리툴바