Conditions: Comparison, AND, OR, BETWEEN, IN

Use

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 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.

SQL Reference Manual, Predicate (predicate)

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Comparison Conditions

SELECT title, name

FROM hotel.customer

WHERE title ='Company'

Selecting the 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)

Switches: 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 Information

More examples for Data Query