[Oracle] BETWEEN 사용

샘플데이터.

EMPLOYEE_ID|EMPLOYEE_NAME|JOB      |MANAGER_ID|HIREDATE           |SALARY|COMMISSION|DEPARTMENT_ID|
-----------|-------------|---------|----------|-------------------|------|----------|-------------|
       7521|WARD         |SALESMAN |      7698|1981-02-22 00:00:00|  1250|       500|           30|
       7566|JONES        |MANAGER  |      7839|1981-04-02 00:00:00|  2975|          |           20|
       7654|MARTIN       |SALESMAN |      7698|1981-09-28 00:00:00|  1250|      1400|           30|
       7698|BLAKE        |MANAGER  |      7839|1981-05-01 00:00:00|  2850|          |           30|
       7782|CLARK        |MANAGER  |      7839|1981-06-09 00:00:00|  2450|          |           10|
       7788|SCOTT        |ANALYST  |      7566|0087-02-13 00:00:00|  3000|          |           20|
       7839|KING         |PRESIDENT|          |1981-11-17 00:00:00|  5000|          |           10|
       7369|SMITH        |CLERK    |      7902|1980-12-17 00:00:00|   800|          |           20|
       7499|ALLEN        |SALESMAN |      7698|1981-02-20 00:00:00|  1600|       300|           30|
       7844|TURNER       |SALESMAN |      7698|1981-09-08 00:00:00|  1500|         0|           30|
       7876|ADAMS        |CLERK    |      7788|0087-02-13 00:00:00|  1100|          |           20|
       7900|JAMES        |CLERK    |      7698|1981-12-03 00:00:00|   950|          |           30|
       7902|FORD         |ANALYST  |      7566|1981-12-03 00:00:00|  3000|          |           20|
       7934|MILLER       |CLERK    |      7782|1982-01-23 00:00:00|  1300|          |           10|

BETWEEN.

기준 필드의 값이 간격안에 있는지 판단할때 사용.

숫자와 문자 그리고 날짜형식 가능.

doc.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions011.htm#SQLRF52164

사용방법.

[NOT] BETWEEN x AND y

예시1.

급여(SALARY)가 3000이상이면서 5000이하인 직원은 누구인가?

SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOB, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 3000 AND 5000

결과1.

EMPLOYEE_ID|EMPLOYEE_NAME|JOB      |SALARY|
-----------|-------------|---------|------|
       7788|SCOTT        |ANALYST  |  3000|
       7839|KING         |PRESIDENT|  5000|
       7902|FORD         |ANALYST  |  3000|

예시2.

급여가 1000이상과 2000이하의 속하지 않는 직원은 누구인가?

SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOB, SALARY
FROM EMPLOYEES
WHERE NOT SALARY BETWEEN 1000 AND 2000

결과2.

EMPLOYEE_ID|EMPLOYEE_NAME|JOB      |SALARY|
-----------|-------------|---------|------|
       7566|JONES        |MANAGER  |  2975|
       7698|BLAKE        |MANAGER  |  2850|
       7782|CLARK        |MANAGER  |  2450|
       7788|SCOTT        |ANALYST  |  3000|
       7839|KING         |PRESIDENT|  5000|
       7369|SMITH        |CLERK    |   800|
       7900|JAMES        |CLERK    |   950|
       7902|FORD         |ANALYST  |  3000|

활용.

해당 기간동 검색할때 활용가능하며 실제로 많은 프로젝트에서 쓰인다.