IN, NOT IN

IN operator retrieves the rows whose column value is in a list.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                      ENAME VARCHAR2(10),
                      HIREDATE DATE);

INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));


SQL> SELECT *
  2  FROM emp
  3  WHERE empno IN (2, 3, 5);

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
         2 ALLEN      20-FEB-81
         3 WARD       22-FEB-81
         5 MARTIN     28-SEP-81

SQL>

NOT IN reverses the IN operator:


SQL> SELECT *
  2  FROM emp
  3  WHERE empno NOT IN (2, 3, 5);

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
         1 SMITH      17-DEC-80
         4 JONES      02-APR-81

SQL>

NULL value and IN operator

NOT IN returns false if a value in the list is null.


SQL> SELECT *
  2  FROM emp
  3  WHERE empno NOT IN (2, 3, 5, NULL);

no rows selected

SQL>
Home »
Oracle »
Select » 

Where:
  1. Where clause
  2. dual table
  3. Comparison Operators
  4. ANY
  5. ALL
  6. LIKE, NOT LIKE
  7. IN, NOT IN
  8. Between...AND, NOT Between...AND
  9. IS NULL, IS NOT NULL
  10. Logical operator AND, OR, NOT
  11. Operator Precedence for AND, OR, NOT
Related: