Conditions: Comparison, AND, OR, BETWEEN, IN 

To select specific rows, you can use the WHERE clause. In WHERE clauses, you can specify search conditions. Some of these conditions are presented below.

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.

SQL Reference Manual, Predicate (predicate)

Comparison Conditions

 

SELECT title, name
  FROM hotel.customer
    WHERE title ='Company'

Selection of customers that are companies

Result

TITLE	NAME
Company	Datasoft
Company	TOOLware

 

SELECT firstname, name, zip
  FROM hotel.customer
    WHERE name > 'Randolph'

Selecting the customers that, in alphabetical order, come after Randolph

Result

FIRSTNAME	NAME	ZIP
Sally	Smith	75243
?	TOOLware	20019

 

See also:

SQL Reference Manual, Comparison Predicate (comparison_predicate)

 

Multiple Conditions: AND, OR

If AND and OR are both used, make sure that AND has a higher priority than OR. You should use parentheses to illustrate the desired multiple conditions. You can also use multiple parentheses. The system then evaluates the data from the innermost to the outermost parentheses.

 

SELECT firstname, name, zip
  FROM hotel.customer
    WHERE (title ='Company') AND (name > 'Randolph')

Selecting the customers that, in alphabetical order, come after Randolph and that are companies

Result

FIRSTNAME	NAME	ZIP
?	TOOLware	20019

See also:

SQL Reference Manual, Search Condition (search_condition)

 

Values in a Range: BETWEEN x AND y

 

SELECT title, name, zip
  FROM hotel.customer
    WHERE zip BETWEEN '10000' AND '30000'

Selection of customers who live in towns with a post code between 10000 and 30000.

Result

TITLE	NAME	ZIP
Mrs	Porter	10580
Mrs	Griffith	20005
Company	TOOLware	20019
Mr	Jenkins	20903

See also:

SQL Reference Manual, BETWEEN Predicate (between_predicate)

 

Values in a Set: IN (x,...)

You can specify the values in the parentheses in any order you desire, since this is a set of values from which a value is to be selected using the IN predicate.

 

SELECT title, firstname, name
  FROM hotel.customer
    WHERE title IN ('Mr', 'Mrs')

Selecting all customers who are natural persons (not companies)

Result

TITLE	FIRSTNAME	NAME
Mrs	Jenny	Porter
Mr	Peter	Brown
Mrs	Rose	Brian
Mrs	Mary	Griffith
Mr	Martin	Randolph
Mrs	Sally	Smith
Mr	Mike	Jackson
Mrs	Rita	Doe
Mr	George	Howe
Mr	Frank	Miller
Mrs	Susan	Baker
Mr	Joseph	Peters
Mr	Antony	Jenkins

See also:

SQL Reference Manual, IN Predicate (in_predicate)

More examples for Data Query