[Oracle] SQL 연산자_02 : 기타 연산자

기타 연산자

- IN,  ANY(SOME),  ALL,  EXISTS,  LIKE,  BETWEEN,  IS(IS NOT)     
 

 

1. IS(IS NOT)

  - NULL값을 비교할때 사용하는 연산자
  - NULL은 '='연산자로 동등성을 평가할 수 없음

 사용예) 상품테이블(PROD)에서 크기 (PROD_SIZE)컬럼에 데이터가 없는 상품을 조회하시오
              Alias는 상품코드, 상품명, 매출가격, 크기
             (조건 : 크기(PROD_SIZE)컬럼에 데이터가 없는 상품)

        SELECT PROD_ID AS 상품코드, 
               PROD_NAME AS 상품명, 
               PROD_PRICE AS 매출가격, 
               PROD_SIZE AS 크기
          FROM PROD
         WHERE PROD_SIZE IS NULL;

 

 

2. IN 연산자

 

  (사용형식)

    컬럼명/수식 IN(값1, 값2,...,값n)

    . '컬러명'의 값이 '값1'이거나 또는 '값2'이거나 또는 '값n'이면 전체가 참을 반환
    . OR연산자로 대신할 수 있음
    . ( )안에 서브쿼리가 올 수 있음
    . '값1, 값2,...,값n'에 포함되지 않은 경우를 판단할때는 NOT IN (값1, 값2,...,값n) 사용
    . 주로 불연속적이거나 불규칙적인 값들에 대한 OR연산이 필요할때 사용
    . IN 연산자에는 '='기능이 내포되어있음 (동등,관계연산자 같이 안 씀)
    

  사용예) HR계정의 부서테이블에서 20,50,60,90,110번 부서의 부서코드, 부서명, 관리사원번호를 조회사히오.

SELECT  DEPARTMENT_ID AS 부서코드, 
                DEPARTMENT_NAME AS 부서명, 
                MANAGER_ID AS 관리사원번호
          FROM  HR.DEPARTMENTS
         WHERE  DEPARTMENT_ID IN(20,50,60,90,110);

 

  사용예) 회원테이블에서 다음을 조회하시오
         회원번호, 회원명, 주민번호, 나이
         단, 나이는 주민증록번로를 이용하시오

    [OR 연산자 사용]

         SELECT  MEM_ID AS 회원번호, 
                 MEM_NAME AS 회원명, 
                 MEM_REGNO1 || '-' || MEM_REGNO2 AS 주민번호, 
                 CASE WHEN SUBSTR (MEM_REGNO2,1,1) = '1' OR
                           SUBSTR (MEM_REGNO2,1,1) = '2' THEN 
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+1900) 
                ELSE       
                            EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+2000)
                END AS 나이
           FROM  MEMBER;

 

    
    [IN 연산자 사용]

         SELECT  MEM_ID AS 회원번호, 
                 MEM_NAME AS 회원명, 
                 MEM_REGNO1 || '-' || MEM_REGNO2 AS 주민번호, 
                 CASE WHEN SUBSTR (MEM_REGNO2,1,1) IN('1','2') THEN 
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+1900) 
                 ELSE       
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+2000)
                 END AS 나이
           FROM  MEMBER;

   
  사용예) 회원테이블에서 다음을 조회하시오
         회원번호, 회원명, 주민번호, 나이,연령대
         단, 나이는 주민증록번로를 이용하시오       

         SELECT  MEM_ID AS 회원번호, 
                 MEM_NAME AS 회원명, 
                 MEM_REGNO1 || '-' || MEM_REGNO2 AS 주민번호, 
                /* CASE WHEN SUBSTR (MEM_REGNO2,1,1) IN('1','2') THEN 
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+1900) 
                 ELSE       
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+2000)
                 END AS 나이*/
                 
                 ROUND((CASE WHEN SUBSTR (MEM_REGNO2,1,1) IN('1','2') THEN 
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+1900) 
                 ELSE       
                           EXTRACT (YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR (MEM_REGNO1,1,2))+2000)
                 END)/10)*10 || '대' AS 연령대
                 
           FROM  MEMBER;

 

---

 

3. ANY(SOME) 연산자

    (사용형식)

  컬럼명 관계연산자 ANY(값1, 값2,...,값n)

      . '컬럼명'의 값이 ANY 다음 ( )안의 값 중 어느 하나DHK 기술된 '관계연산자'의
         조건을 만족하면 결과가 참이되는 연산자
      .  ANY, SOME 은 동일함
      . ANY 앞의 관계연산자가 '='인 경우가 IN연산자 임
      
  사용예) 사원테이블에서 20번 부서의 최소급여, 40번 부서의 최소급여, 70번 부서에 최소급여보다
         더 적은 급여의 사원을 조회하시오
         Alias는 사원번호, 사원명, 부서번호, 급여,
         (20번 : 6000 / 40번 : 6500 / 70번 : 10000)

         SELECT EMPLOYEE_ID AS 사원번호, 
                EMP_NAME AS 사원명, 
                DEPARTMENT_ID AS 부서번호, 
                SALARY AS 급여
           FROM HR.EMPLOYEES
          WHERE SALARY > ANY(6000,6500,10000)
          ORDER BY 4;
          
         [조건] 서브쿼리
         SELECT  MIN (SALARY)
           FROM  HR.EMPLOYEES
          WHERE  DEPARTMENT_ID IN (20,40,70)

          
  사용예) 대전거주 회원 중 충남에 거주하는 회원들의 가장 적은 마일리지보다 더 많은 마일리지를 
         보유한 회원의 회원번호, 회원명, 마일리지를 조회하시오.

        [조건] 서브쿼리
         SELECT MEM_MILEAGE
           FROM MEMBER
          WHERE MEM_ADD1 LIKE '충남%'
          
          
         SELECT MEM_ID AS 회원번호, 
                MEM_NAME AS 회원명, 
                MEM_MILEAGE AS 마일리지
           FROM MEMBER
          WHERE MEM_ADD1 LIKE '대전%'
            AND MEM_MILEAGE > ANY( SELECT MEM_MILEAGE
                                     FROM MEMBER
                                    WHERE MEM_ADD1 LIKE '충남%')
          ORDER BY 3;

 

 

---

 

4. ALL 연산자

 

    (사용형식)

    컬럼명 관계연산자 ALL(값1, 값2,...,값n)

      . '컬럼명'의 값이 ALL 다음 ( )안의 값 중 어느 하나와 기술된 '관계연산자'의
         조건을 만족하면 결과가 참이되는 연산자
      . 관계연산자에 '='은 사용할 수 없다
      
  사용예) 대전거주 회원 중 충남에 거주하는 회원들의 가장 큰 마일리지보다 더 많은 마일리지를 
         보유한 회원의 회원번호, 회원명, 마일리지를 조회하시오.

         SELECT MEM_ID AS 회원번호, 
                MEM_NAME AS 회원명, 
                MEM_MILEAGE AS 마일리지
           FROM MEMBER
          WHERE MEM_ADD1 LIKE '대전%'
            AND MEM_MILEAGE < ALL( SELECT MEM_MILEAGE
                                     FROM MEMBER
                                    WHERE MEM_ADD1 LIKE '충남%')
          ORDER BY 3;

 

---

 

 5. LIKE 연산자

 

   - 문자열 연산자 (와일드 카드 %  _)
   - 패턴을 비교하는 연산자
   - 와일드카드로 '%'와 '_'를 사용함

   - '%'
    . '%'가 사용된 이후 모든 문자열과 대응됨
    . ex) '김%' -> '김'으로 시작하는 모든 문자열을 모두 허용됨(NULL 포함)
          '%김' -> '김'으로 끝나는 모든 문자열을 모두 허용
          '%김%' -> 단어 중 '김' 글자가 포함된 모든 문자열을 모두 허용

   - '_'
    . '_'가 사용된 이후 모든 문자열과 대응됨
    . ex) '김_' -> '김'으로 시작하는 2글자인 문자열과 대응
          '_김' -> '김'으로 끝나는 2글자의 문자열과 대응
          '_김_' -> 3글자로 구성된 단어 중 '김' 글자가 포함된 문자열은 모두 허용

  - LIKE 연산자는 문자열 비교에 사용되며, 많은 결과를 산출하기 때문에 쿼리의 효율성은 낮은편임
  - 문자열이외의 자료(날짜, 숫자)에 사용은 자제할 것
  
  사용예) 회원테이블에서 거주지가 대전인 회원들의 이름, 주소,직업 마일리지를 조회하시오

        SELECT MEM_NAME AS 이름, 
               MEM_ADD1 || ' ' || MEM_ADD2 AS 주소, --문자열 연결 '||'
               MEM_JOB AS 직업, 
               MEM_MILEAGE AS 마일리지
          FROM MEMBER
         WHERE MEM_ADD1 LIKE '대전%';
         --WHERE SUVSTR(MEM_ADD1,1,2) = '대전';

  
  사용예) 장바구니에서 2020년 5월 판매 정보를 조회하시오
         Alias는 회원번호, 상품번호, 구매수량

         SELECT CART_MEMBER AS 회원번호, 
                CART_PROD AS 상품번호, 
                CART_QTY AS 구매수량
           FROM CART
          WHERE CART_NO LIKE '202005%'

         
  사용예) 매입테이블에서 2020년 2월 매입정보를 조회하시오.
         Alias는 일자, 상품번호, 매입수량, 매입금액
         매입금액 = 수량 * 단가

         SELECT BUY_DATE AS 일자,
                BUY_PROD AS 상품번호, 
                BUY_QTY AS 매입수량,
                BUY_COST * BUY_QTY AS 매입금액 
           FROM BUYPROD
          WHERE BUY_DATE LIKE '2020/02%'
          
                --TO_CHAR(BUY_DATE, 'YYYYMMDD') LIKE '202002%;
                --BUY_DATE BETWEEN TO_DATE('20200201') 
                --AND LAST_DATE(TO_DATE('20200201'));