Entering content frame

This graphic is explained in the accompanying text Negative Conditions: NOT Locate the document in the library structure

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, Structure linkSearch 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, Structure linkBETWEEN 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, Structure linkIN 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, Structure linkLIKE 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, Structure linkNULL Predicate (null_predicate)

SQL Reference Manual, Structure linkPredicate (predicate)

More examples for Data Query

 

Leaving content frame