SQL NOT

by

The SQL NOT operator is used to invert the result of an expression.

Truth table for SQL NOT:

Expression Value NOT
True False
False True
Unknown Unknown

SQL NOT Syntax

SELECT * 
FROM TableName
WHERE NOT predicate

SQL NOT Example

Table: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
206 Gecrrcc Srlkrt 62000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000
304 Jyoysww Osk 49000

Example 1: SQL NOT　IN
Select all employees whose last name is not Lnanyo, Srlkrt or Osk:

SELECT * 
FROM Employees
WHERE NOT LastName IN ('Lnanyo', 'Srlkrt', 'Osk')

We can also write the statement:

SELECT * 
FROM Employees
WHERE LastName NOT IN ('Lnanyo', 'Srlkrt', 'Osk')

The result will look like:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
205 Syllauu Dfaafk Finance 57000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000

Example 2: SQL NOT　LIKE
Select all employees whose first name is not start with “J”:

SELECT * 
FROM Employees
WHERE NOT FirstName LIKE 'J%'

We can also write the statement:

SELECT * 
FROM Employees
WHERE FirstName NOT LIKE 'J%'

The result will look like:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
205 Syllauu Dfaafk Finance 57000
206 Gecrrcc Srlkrt 62000
303 Dnnaao Errllov Development 55000

Example 3: SQL IS NOT NULL
Select all employees who is a member of a department:

SELECT * 
FROM Employees
WHERE NOT Department IS NULL

We can also write the statement:

SELECT * 
FROM Employees
WHERE Department IS NOT NULL

The result will look like:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000

Leave a Comment