Exclusive comparisons

Retrieving Information from a Table › Retrieving Selected Rows › Exclusive comparisons

Exclusive comparisons

Using NOT

You can retrieve all rows that are exceptions to the comparison expression by using the keyword NOT. For example, you can retrieve information about all employees except those that work in departments 6666 and 3100.

A few examples appear below:

Example	Meaning
where not (deptid < 4000)	All rows where the department id is greater than or equal to 4000
where deptid = 4000 and not (empid = 0007)	All rows where the department id is 4000 except the row with employee id 0007

Example

List all employees except those with manager id 0007:

select firstname, lastname, mgrid
 from emp
 where not (mgrid = 0007)
 order by mgrid ! display

EMP REPORT mm/dd/yy FIRSTNAME LASTNAME MGRID ────────── ─────────────── ────── LARRY LITERATA 0003 RALPH TYRO 0003 JULIE JENSEN 0003 JAMES JACOBI 0003 VLADIMIR HEAROWITZ 0003 JANE DOUGH 0003 PERCY GRANGER 0003 JAMES GALLWAY 0003 MONTE BANK 0030 JENNIFER GARFIELD 0030 HENRIETTA HENDON 0030 JOHN RUPEE 0030 THEMIS PAPAZEUS 0030 ROBBY WILDER 0030 - 1 -