오라클, SQL 조건연산자를 이용한 조건검색, LIKE, IS NULL
http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=604
3.2.6 SQL 조건연산자를 이용한 조건검색
BETWEEN A AND B | A와 B의 사이의 값, 경계 A,B 도 포함 A보다 크거나 같고 B보다 적거나 같다. (AND로 해석) |
IN(A, B, …, N) | A, B, … N 중의 하나와 일치하면 참 (OR로 해석) |
-- MYEMP1 테이블에서 입사일자가 2013년 10월25일~2014년12월31일 사이의 입사자 수 출력, 먼저 두가지 쿼리를 보자. SQL> SELECT COUNT(*) FROM MYEMP1 WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26' AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';
COUNT(*) ---------- 1440288
경 과: 00:00:16.60
SQL> SELECT COUNT(*) FROM MYEMP1 WHERE HIREDATE >= '13/10/26' AND HIREDATE < '15/01/01';
COUNT(*) ---------- 1440288
경 과: 00:00:17.20
-- 위 두 쿼리의 차이는 WHERE절 HIREDATE칼럼에 SQL함수 TO_CHAR가 적용되었느냐의 여부다. 현재 MYEMP1의 HIREDATE 칼럼에는 검색을 빠르게 해주는 인덱스가 생성되어 있지 않다. 인덱스라는 것을 만든 후 두 쿼리를 다시 실행하여 실행시간의 차이에 대해 확인하자. SQL> CREATE INDEX IDX_MYEMP1_HIREDATE ON MYEMP1(HIREDATE);
-- 아래 쿼리는 HIREDATE 칼럼에 SQL함수 TO_CHAR가 적용되어 칼럼값에 변형이 생겨 만들어 놓은 인덱스를 이용하지 못한다. SQL> SELECT COUNT(*) FROM MYEMP1 WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26' AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';
COUNT(*) ---------- 1440288
경 과: 00:00:16.53
Execution Plan -------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 9 25678 00:05:09 1 SORT AGGREGATE 1 9 | * 2 | TABLE ACCESS FULL MYEMP1 25000 219K 25678 00:05:09 ---------------------------------------------------------------------------------------------
-- 아래 쿼리의 실행계획을 보면 인덱스를 경유하여 데이터를 추출한 것을 알 수있다. 두 쿼리중 어떤 쿼리를 사용해야 할지 고민해 보자. SQL> SELECT COUNT(*) FROM MYEMP1 WHERE HIREDATE >= '13/10/26' AND HIREDATE < '15/01/01';
COUNT(*) ---------- 1440288
경 과: 00:00:00.71 -- 1초도 걸리지 않는다.
Execution Plan ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 9 | 123 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_MYEMP1_HIREDATE | 25000 | 219K| 123 (1)| 00:00:02 | ------------------------------------------------------------------------------------------
-- BETWEEN ~ AND ~는 AND연산으로 변형할 수 있다. SQL> select empno, ename, sal from emp where sal between 1000 and 5000;
EMPNO ENAME SAL ---------- -------------------- -------- 7499 ALLEN 1,600 7521 WARD 1,250 7566 JONES 2,975 7654 MARTIN 1,250
SQL> select empno, ename, sal from emp where sal >= 1000 and sal <= 5000;
-- EMP 테이블에서 부서코드가 10 or 20 or 30인 사원의 사번, 이름, 급여를 출력하라. IN 연산자는 or로 풀어쓸 수 있다. SQL> select empno, ename, sal from emp where deptno in (10, 20, 30);
EMPNO ENAME SAL ---------- -------------------- -------- 7369 SMITH 800 7499 ALLEN 1,600 7521 WARD 1,250 ……
SQL> select empno, ename, sal from emp where deptno = 10 or deptno = 20 or deptno = 30;
-- MYEMP1, MYSUGANG1 테이블에서 두과목 이상 수강한 사원의 사번, 이름을 출력하라. SQL> SELECT EMPNO, ENAME FROM MYEMP1 E WHERE EMPNO IN ( SELECT EMPNO FROM MYSUGANG1 S GROUP BY EMPNO, LECTURE_ID HAVING COUNT(*) > 1 )
|
LIKE | 패턴 비교,부분적으로 일치(%, _ )하면 참 |
IS NULL | NULL 이면 참 |
IS NOT NULL | NULL이 아니면 참 |
LIKE : Query 조건으로 와일드카드를 사용 할 때 이용 한다. 와일드 카드인 ‘%’는 0개 이
상의 문자, ‘_’(Under bar)는 한 개의 문자를 의미 한다. 또한 비교하려는 문자에 ‘%’, ‘_’등
이 포함되어 있어 와일드 카드로서의 의미보다는 문자로서(리터럴값 자체)의 의미를 나타내기 위해서는 ESCAPE 옵션을 이용 한다.
--아래는 이름이 S로 시작하는 사원의 이름, 급여를 선택하는 질의임. SQL> select ename, sal from emp where ename like 'S%'; ENAME SAL ---------- ---------- SMITH 800 SCOTT 3000
SQL> select ename from emp; ENAME ---------- SMITH …… JONES JAMES ADAMS
SQL> select ename, sal from emp where ename like 'J___S'; -- 언더바 3개(첫글자는 J, 이후 세 글자 오고 마지막은 S) ENAME SAL ---------- ---------- JONES 2975 JAMES 950
SQL> insert into emp (empno, ename, sal) values (7777, 'J_SARA',700); SQL> insert into emp (empno, ename, sal) values (7778, 'J_PAGE',800); SQL> commit;
-- 이름이 J_로 시작하는 직원의 이름, 급여를 출력하세요 (잘못된 문장) SQL> select ename, sal from emp where ename like 'J_%';
ENAME SAL ---------- ---------- JONES 2975 JAMES 950 J_SARA 700 J_PAGE 800
-- 아래처럼 ESCAPE를 사용해야 한다. SQL> select ename, sal from emp where ename like 'J\_%' escape '\'; ENAME SAL ---------- ---------- J_SARA 700 J_PAGE 800
SQL> 2 where ename like 'J|_%' escape '|'; SQL> / ENAME SAL ---------- ---------- J_SARA 700 J_PAGE 800
-- MYEMP1 테이블에서 이름이 ‘홍’으로 시작하는 사원들의 급여 최대값을 구하라. SQL> SELECT MAX(SAL) FROM MYEMP1 WHERE ENAME LIKE '홍%'
MAX(SAL) ---------- 5999995 |
IS[NOT] NULL : NULL 값인지의 여부를 검사, NULL을 검사하기 위한 유일한 연산자, 원래 NULL을 가지고 어떠한 연산이라도 할 수 없다. NULL인지 비교하기 위해 ‘=’, ‘!=’ 연산자를 이용하는 것은 잘못된 방식이다.
SQL> select count(*) from emp 2 where comm is null;
COUNT(*) ---------- 11 SQL> 2 where comm = NULL SQL> /
COUNT(*) ---------- 0
-- MYEMP1 테이블의 COMM 칼럼에 인덱스를 만들자. SQL> CREATE INDEX IDX_MYEMP1_COMM ON MYEMP1(COMM);
-- MYEMP1 테이블에서 COMM이 NULL인 사원의 수를 출력하세요. IS NULL은 인덱스를 사용하지 못한다. 인덱스 영역에는 NULL값은 보관하지 않는다. SQL> SELECT COUNT(*) FROM MYEMP1 WHERE COMM IS NULL;
COUNT(*) ---------- 8333335
경 과: 00:00:16.63
Execution Plan --------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 13 25257 00:05:04 1 SORT AGGREGATE 1 13 * 2 TABLE ACCESS FULL MYEMP1 500K 6347K 25257 00:05:04
-- MYEMP1 테이블에서 COMM이 NULL이 아닌 사원의 수를 출력하세요. IS NOT NULL인 경우에는 인덱스를 사용함을 확인하고 실행시간도 위 쿼리와 비교하라. SQL> SELECT COUNT(*) FROM MYEMP1 WHERE COMM IS NOT NULL;
COUNT(*) ---------- 1666667
경 과: 00:00:00.49
Execution Plan ----------------------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time ----------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 13 1056 00:00:13 1 SORT AGGREGATE 1 13 * 2 INDEX FAST FULL SCAN IDX_MYEMP1_COMM 500K 6347K 1056 00:00:13 ------------------------------------------------------------------------------------------------------------
|
