Oracle BETWEEN & IN & NULL

 如果我想要在dept table 找 deptno 為20 或 30 的ename, hiredate資料

照之前學過可以使用or

query:

SELECT ename, hiredate FROM EMP

WHERE deptno = 20 

OR deptno = 30

不過還有一種方式是用in

是一樣的結果

SELECT ename, hiredate FROM EMP

WHERE DEPTNO IN (20,30)

如果想要找ename為FORD,SMITH,ALLEN,WARD,MARTIN的ename, hiredate資料

query:

SELECT ename, hiredate FROM EMP

WHERE ename IN ('FORD', 'SMITH', 'ALLEN', 'WARD', 'MARTIN')

如果想要找ename為非FORD,SMITH,ALLEN,WARD,MARTIN的ename, hiredate資料

SELECT ename, hiredate FROM EMP

WHERE ename NOT IN ('FORD', 'SMITH', 'ALLEN', 'WARD', 'MARTIN')

接下來是BETWEEN

BETWEEN 會跟 AND 連用表示 在...之間

例如想找hiredate在1981/9/28~1982/12/9之間的資料

query:

SELECT * 

FROM emp

WHERE hiredate BETWEEN to_date('1981-09-28','yyyy-mm-dd') AND to_date('1982-12-09','yyyy-mm-dd')

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	1981/11/17	5000	-	10
7788	SCOTT	ANALYST	7566	1982/12/09	3000	-	20
7902	FORD	ANALYST	7566	1981/12/03	3000	-	20
7654	MARTIN	SALESMAN	7698	1981/09/28	1250	1400	30
7900	JAMES	CLERK	7698	1981/12/03	950	-	30
7934	MILLER	CLERK	7782	1982/01/23	1300	-	10

或是想找 sal 在 1000~2000之間的資料

query:

SELECT * FROM emp

WHERE sal BETWEEN 1000 AND 2000

如果想找sal 在1000~2000這個區間外的資料

query:

SELECT * FROM emp

WHERE sal NOT BETWEEN 1000 AND 2000

如果想找emp table 內 comm 是 null的資料

query:

SELECT * 

FROM emp

WHERE comm IS  NULL;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	1981/11/17	5000	-	10
7698	BLAKE	MANAGER	7839	1981/05/01	2850	-	30
7782	CLARK	MANAGER	7839	1981/06/09	2450	-	10
7566	JONES	MANAGER	7839	1981/04/02	2975	-	20
7788	SCOTT	ANALYST	7566	1982/12/09	3000	-	20
7902	FORD	ANALYST	7566	1981/12/03	3000	-	20
7369	SMITH	CLERK	7902	1980/12/17	800	-	20
7876	ADAMS	CLERK	7788	1983/01/12	1100	-	20
7900	JAMES	CLERK	7698	1981/12/03	950	-	30
7934	MILLER	CLERK	7782	1982/01/23	1300	-	10

同理,如果想找emp table 內 comm 不是 null的資料

query:

SELECT * 

FROM emp

WHERE comm IS NOT NULL;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	1981/02/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	1981/09/28	1250	1400	30
7844	TURNER	SALESMAN	7698	1981/09/08	1500	0	30