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 require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

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, DC, 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
Anthony	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
Anthony	Jenkins

See also:

SQL Reference Manual, NULL Predicate (null_predicate)

SQL Reference Manual, Predicate (predicate)

More examples for Data Query