SQL NOT

 

SQL NOT


Summary: in this tutorial, you will learn how to use the SQL NOT operator to negate a Boolean expression in the WHERE the clause of the SELECT statement.

You have learned how to use various logical operators such as ANDORLIKEBETWEENIN, and EXISTS. These operators help you to form flexible conditions in the WHERE clause.

To reverse the result of any Boolean expression, you use the NOT operator. The following illustrates how to use the NOT operator.

NOT [Boolean_expression]

The following table shows the result of the NOT operator.

NOT
TRUEFALSE
FALSETRUE
NULLNULL

SQL NOT operator examples

We are going to use the employees table for the demonstration of the NOT operator.

employees_table

The following statement retrieves all employees who work in the department id 5.

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 5 ORDER BY salary;
SQL NOT operator example

To get the employees who work in the department id 5 and with a salary not greater than 5000.

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 5 AND NOT salary > 5000 ORDER BY salary;
SQL NOT operator with greater than example

SQL NOT with IN operator example

To negate the IN operator, you use the NOT operator. For example, the following statement gets all the employees who are not working in department 1, 2, or 3.

SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id NOT IN (1, 2, 3) ORDER BY first_name;
SQL NOT IN operator example

SQL NOT LIKE operator example

You can negate the LIKE operator by using the NOT LIKE. For example, the following statement retrieves all the employees whose first names do not start with the letter D.

SELECT first_name, last_name FROM employees WHERE first_name NOT LIKE 'D%' ORDER BY first_name;
SQL NOT LIKE operator example

SQL NOT BETWEEN example

The following example shows you how to use the NOT to negate the BETWEEN operator to get employees whose salaries are not between 5,000 and 1,000.

SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 3000 AND 5000 ORDER BY salary;

SQL NOT EXISTS example

See the following employees and dependents tables:

employees_dependents_tables

The following query uses the NOT EXISTS operator to get the employees who do not have any dependents.

SELECT employee_id, first_name, last_name FROM employees e WHERE NOT EXISTS ( SELECT employee_id FROM dependents d WHERE d.employee_id = e.employee_id );

Now you should know how to use the NOT operator to negate a Boolean expression.

Reactions

Post a Comment

0 Comments