not in (1,2,3,NULL) : NOT « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. NOT
SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)
  6  , constraint  R_PK        primary key (attendee,course,begindate)
  7  ) ;

Table created.

SQL>
SQL> insert into registrations values (2,'SQL',date '2009-04-12',4   );

1 row created.

SQL> insert into registrations values (14,'SQL',date '2009-04-12',5   );

1 row created.

SQL> insert into registrations values (6,'SQL',date '2009-04-12',4   );

1 row created.

SQL> insert into registrations values (11,'SQL',date '2009-04-12',2   );

1 row created.

SQL> insert into registrations values (8,'SQL',date '2009-10-04',NULL);

1 row created.

SQL> insert into registrations values (9,'SQL',date '2009-10-04',3   );

1 row created.

SQL> insert into registrations values (13,'SQL',date '2009-10-04',4   );

1 row created.

SQL> insert into registrations values (13,'SQL',date '2009-12-13',NULL);

1 row created.

SQL> insert into registrations values (6,'SQL',date '2009-12-13',NULL);

1 row created.

SQL> insert into registrations values (3,'OAU',date '2009-08-10',4   );

1 row created.

SQL> insert into registrations values (12,'OAU',date '2009-08-10',4   );

1 row created.

SQL> insert into registrations values (13,'OAU',date '2009-08-10',5   );

1 row created.

SQL>
SQL> select *
  2  from   registrations
  3  where  evaluation not in (1,2,3,NULL);

no rows selected

SQL>
SQL>
SQL> drop table registrations;

Table dropped.








2.25.NOT
2.25.1.NOT to negate a boolean value
2.25.2.Or, and, not
2.25.3.NOT operator has higher priority than AND
2.25.4.Not empty(null)
2.25.5.Not equals
2.25.6.Not equals: not vs <>
2.25.7.not in (1,2,3,NULL)
2.25.8.not in vs not exists
2.25.9.Not has the lowerest priority
2.25.10.Not(condition1 and condition2)
2.25.11.Using Not in and subquery