. RockNets | How to install CentOS 7 on Virtualbox(with screenshots)

Home / Tutorial and Knowledge base / Oracle tutorial / not

    Oracle - NOT Condition



    In Oracle PL/SQL DML (SELECT, INSERT, UPDATE, DELETE), you may use the NOT condition to negate a condition in the WHERE clause.

    In this tutorial, you will learn how to use the "NOT" condition In Oracle PL/SQL statement. We will walkthrough the "NOT" syntax with detailed explanations and examples.

    Syntax


    The syntax of a the NOT operator is in the follow two forms:



    Syntax
    SELECT
    column1, column2....

    FROM
    table_name

    WHERE NOT (
    condition
    );
    Parameters Explanations

    column1, column2....
    The original column name of the table defined in the database schema

    table_name
    The original table name defined in the database schema

    condition
    The opposite of the condition must be met for the records to be returned in the result set.



    Examples - How to use the NOT with the IN condition with character value


    In this tutorial, we will use the well-known Northwind sample database for demonstration purpose. Below is a portion of data selected from the employee table:

    idlast_namefirst_namejob_title
    1 FreehaferNancySales Representative
    2CenciniAndrewVice President, Sales
    3KotasJanSales Representative
    4SergienkoMariyaSales Representative
    5ThorpeStevenSales Manager
    6NeipperMichaelSales Representative
    7ZareRobertSales Representative
    8GiussaniLauraSales Coordinator
    9Hellung-LarsenAnneSales Representative


    Here is the example of selecting the total number of employee of each job title

    SELECT id, last_name, first_name, job_title
    FROM employees
    WHERE first_name in ('Nancy','Anne');


    The result is:
    idlast_namefirst_namejob_title
    2CenciniAndrewVice President, Sales
    3KotasJanSales Representative
    4SergienkoMariyaSales Representative
    5ThorpeStevenSales Manager
    6NeipperMichaelSales Representative
    7ZareRobertSales Representative
    8GiussaniLauraSales Coordinator
    In the above example, we have selected the data from the employee table and the first_name must NOT contains the value 'Nancy' or 'Anne'. In other words, for all the employee without first name in this two values will be returned.

    Examples - How to use the NOT with the IN condition with numeric value



    Next, we can explore how to use the NOT with the IN condition with the numeric value. Let's consider to use the employee table again.



    Now, let's try to issue the following SQL:

    SELECT id, last_name, first_name, job_title
    FROM employees
    WHERE id NOT in (1, 3, 5, 7, 9);


    The result is:
    idlast_namefirst_namejob_title
    2CenciniAndrewVice President, Sales
    4SergienkoMariyaSales Representative
    6NeipperMichaelSales Representative
    8GiussaniLauraSales Coordinator


    In the above example, we have tried to use the NOT with the IN condition with numeric value. Only the employee records with id NOT in the numeric value specified will be returned in the query.









Time and Weather

Calculators

Converters

Online Tools

Tutorial and Knowledge Base

Search the site