SQL NOT operator in DB2 for i SQL

SQL NOT operator in DB2 for i SQL

The SQL NOT operator is used in combination with other operators to give the opposite result/negative result.

Syntax using NOT operator

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition

Example using NOT operator

Select only those customers that are NOT from City HISAR from the Customer Table:

SELECT * FROM Customer WHERE NOT CUSTCITY = 'HISAR'

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
    10   TE_ST                 NOIDA                 UTTARPRADESH          INDIA      
    11   T%EST                 NOIDA                 UTTARPRADESH          INDIA      

Here, in this example NOT operator is used in combination with the = (equal) operator but it can be used with other SQL operators as well. Let's discuss them as well.

NOT LIKE

Select those customer whose name does not start with letter 'T'

SELECT * FROM Customer WHERE CUSTNAME NOT LIKE 'T%'

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      

NOT BETWEEN

Select those customer whose Customer Id not between 8 and 11

SELECT * FROM Customer WHERE CUSTID NOT BETWEEN 8 AND 11

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      

NOT IN

Select those customer that are not from state HARYANA or BIHAR

SELECT * FROM Customer WHERE CUSTSTATE NOT IN ('HARYANA','BIHAR')

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
    10   TE_ST                 NOIDA                 UTTARPRADESH          INDIA      
    11   T%EST                 NOIDA                 UTTARPRADESH          INDIA      

NOT GREATER THAN

Select those customer that have a customer id not greater than 5

SELECT * FROM Customer WHERE NOT CUSTID > 5 

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      

NOT LESS THAN

Select those customer that have a customer id not less than 5

SELECT * FROM Customer WHERE NOT CUSTID < 5

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     5   Yatin                 IDUKKI                KERALA                INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
    10   TE_ST                 NOIDA                 UTTARPRADESH          INDIA      
    11   T%EST                 NOIDA                 UTTARPRADESH          INDIA