Negative Conditions: NOT 
To obtain the opposite of a particular condition, you have to place the keyword NOT before the relevant expression. If you want to negate a compound expression, you have to place it in parentheses.
Prerequisites
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
Activities
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
Negating an Expression
SELECT name, state
FROM hotel.city
WHERE NOT (state = 'CA' OR state = 'WA' OR state = 'IL' OR state = 'NY')
Selecting cities that are not in the states of CA, WA, IL or NY
Result
NAME |
STATE |
Silver Spring |
MD |
Daytona Beach |
FL |
Deerfield Beach |
FL |
Clearwater |
FL |
Cincinnati |
OH |
Detroit |
MI |
New Orleans |
LA |
Dallas |
TX |
Portland |
OR |
See also: SQL Reference Manual, Search Condition (search_condition)
When the BETWEEN, IN, LIKE and NULL predicates are used, you can place NOT before the predicate or immediately before the relevant keyword (BETWEEN, IN, LIKE, NULL).
NOT BETWEEN
SELECT name, state
FROM hotel.city
WHERE NOT (state BETWEEN 'CA' AND 'NY')
or
SELECT name, state
FROM hotel.city
WHERE state NOT BETWEEN 'CA' AND 'NY'
Selecting cities that are not in the states of CA, FL, IL, LA, MD, MI or NY
Result
NAME |
STATE |
Seattle |
WA |
Seattle |
WA |
Seattle |
WA |
Cincinnati |
OH |
Dallas |
TX |
Portland |
OR |
See also: SQL Reference Manual, BETWEEN Predicate (between_predicate)
NOT IN
SELECT name, state
FROM hotel.city
WHERE NOT (state IN ('CA','IL','NY'))
or
SELECT name, state
FROM hotel.city
WHERE state NOT IN ('CA','IL','NY')
Selecting cities that are not in the states of CA, IL or NY
Result
NAME |
STATE |
Seattle |
WA |
Seattle |
WA |
Seattle |
WA |
Silver Spring |
MD |
Daytona Beach |
FL |
Deerfield Beach |
FL |
Clearwater |
FL |
Cincinnati |
OH |
Detroit |
MI |
New Orleans |
LA |
Dallas |
TX |
Portland |
OR |
See also: SQL Reference Manual, IN Predicate (in_predicate)
NOT LIKE
SELECT firstname, name
FROM hotel.customer
WHERE NOT (firstname LIKE '%e%')
or
SELECT firstname, name
FROM hotel.customer
WHERE firstname NOT LIKE '%e%'
Selecting the customers whose first names do not contain e
Result
FIRSTNAME |
NAME |
Mary |
Griffith |
Martin |
Randolph |
Sally |
Smith |
Rita |
Doe |
Frank |
Miller |
Susan |
Baker |
Antony |
Jenkins |
See also: SQL Reference Manual, LIKE Predicate (like_predicate)
NOT NULL
SELECT firstname, name
FROM hotel.customer
WHERE NOT (firstname IS NULL)
or
SELECT firstname, name
FROM hotel.customer
WHERE firstname IS NOT NULL
Selecting the customers with a first name, that is, customers who are not companies
Result
FIRSTNAME |
NAME |
Jenny |
Porter |
Peter |
Brown |
Rose |
Brian |
Mary |
Griffith |
Martin |
Randolph |
Sally |
Smith |
Mike |
Jackson |
Rita |
Doe |
George |
Howe |
Frank |
Miller |
Susan |
Baker |
Joseph |
Peters |
Antony |
Jenkins |
See also: SQL Reference Manual, NULL Predicate (null_predicate), Predicate (predicate)
More Information
More examples for Data Query