IN,NOT IN 操作符,用于一次匹配多个条件。
例:查询部门编号为10、20、40的员工信息;
SQL@itkaifang> SELECT empno,ename,sal,deptno FROM emp WHERE deptno IN (10,20,40); EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7369 SMITH 800 20 7566 JONES 2975 20 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7876 ADAMS 1100 20 7902 FORD 3000 20 7934 MILLER 1300 10 8 rows selected. SQL@itkaifang>
例:查询部门编号为10、40以外的员工信息;
SQL@itkaifang> SELECT empno,ename,sal,deptno FROM emp WHERE deptno NOT IN (10,40); EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7369 SMITH 800 20 7499 ALLEN 1600 30 7521 WARD 1250 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7698 BLAKE 2850 30 7788 SCOTT 3000 20 7844 TURNER 1500 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 11 rows selected. SQL@itkaifang>