Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Selecting Specific Rows


Rows are selected in the SELECT statement according to the search condition in the WHERE clause. This condition relates column value(s) to expressions.

Comparison Conditions and WHERE

Comparison operators that may be used in the WHERE clause are:

Operator
Explanation
 =
equal to
 <>
not equal to
 <
less than
 <=
less than or equal to
 >
greater than
 >=
greater than or equal to

Comparisons can be combined in the search condition using the logical operators AND and OR, and reversed using NOT.

Each comparison must be expressed in full; for example

 WHERE price >= 10.00 AND price <= 20.00
 

may not be expressed as

 WHERE price >= 10.00 AND <= 20.00

Comparing Character Strings

Character strings are compared character by character from left to right.

If strings are of different lengths, the shorter is conceptually padded to the right with blanks before the comparison is made (i.e. character difference takes precedence over length difference).

The default collation for characters is an extended Latin1 character set as defined by ISO 8859-1, see the Mimer SQL Reference Manual, Appendix B, Character Sets for the exact sequence.

For more information on collations, see the Mimer SQL User's Manual, Collations.

Retrieve the European Article Number (EAN), price and number in stock for all available items costing 100 euros and more:
 SELECT ean_code, price, stock
     FROM items
     WHERE status = 'A'
     AND price >= 100.00;
Returns:

EAN_CODE
PRICE
STOCK
790051157548
115.98
14
790051155506
279.98
16
790051595920
227.98
10

Comparing Temporal Data

When stating conditions on temporal data in tables, datetime and interval literals can be specified. There are also the pseudo literals CURRENT_DATE, LOCALTIME and LOCALTIMESTAMP which read the server clock and return that value.

If there is more than one occurrence of these pseudo literals in a statement the clock is only read once.

List the EAN and price for any items released on the 5th September 1994:
 SELECT ean_code, price
    FROM items
    WHERE release_date = DATE'1994-09-05';
Returns:

EAN_CODE
PRICE
9780001006041
7.00

Retrieve the EAN and price for any items with a release date in the future:
 SELECT ean_code, price
    FROM items
    WHERE release_date > CURRENT_DATE;
Returns:

EAN_CODE
PRICE
7298976754871
13.98
7464376662256
15.98
9781990789861
13.99
9781993789639
6.99

For an example of interval literals, see Datetime Arithmetic and Functions.

Pattern Conditions

LIKE is used to search for character strings that match a specified pattern.

Patterns in the LIKE condition are written with wildcard characters (also called meta-characters):

Pattern
Explanation
_
(underscore) stands for any single character
%
stands for any sequence of zero or more characters

(Wildcards only have significance in LIKE predicates.)

Find all currencies whose names include the string "Islands":
 SELECT currency
    FROM currencies
    WHERE currency LIKE '%Islands%';
Returns:
 CURRENCY
 Falkland Islands Pounds
 Cayman Islands Dollars
 Solomon Islands Dollars
Find all formats whose names do not contain the string "Audio":
 SELECT format
    FROM formats
    WHERE format NOT LIKE '%Audio%';
Returns:
 FORMAT
 Cassette
 Vinyl
 Hardcover
 Paperback
 DVD Video
 Video

Remember that character strings in Mimer SQL statements are always written within single quotation marks (' ').

A LIKE predicate where the pattern string does not contain any wildcard characters is essentially equivalent to a basic predicate using the '=' operator, except that comparison strings in an '='comparison are conceptually padded with blanks whereas those in the LIKE comparison are not.

For example:
 'Dollars ' =    'Dollars'  is true
 'Dollars ' LIKE 'Dollars ' is true
 'Dollars ' LIKE 'Dollars%' is true
 

but

 'Dollars ' LIKE 'Dollars' is false
 

The LIKE predicate may include an ESCAPE clause defining a character which is used to `escape' wildcard characters. A wildcard character immediately following an escape character is taken at face value. See the Mimer SQL Reference Manual, The LIKE Predicate, for more details.

More about Searching for Character Strings

Some other examples of searching for character strings are:

 LIKE '%P%'
matches any string that contains an upper-case `P'.
 LIKE '%P%' COLLATE english_1
matches any string that contains an upper or lower case `P'.
 LIKE '_abc' 
matches any four letter character string ending with lower-case `abc'.
 LIKE '%A\%' ESCAPE '\' 
matches any string ending with `A%'.
 LIKE 'D_d_' 
matches any four letter string with D and d in the first and third positions respectively. Examples of possible values: Dude, Dads.

Set Conditions

IN and Not IN

The operator IN finds the values that are contained in a set of values. The set is given as a comma-separated list enclosed in parentheses.

NOT IN finds values which are not contained in the specified set.

Which currencies are represented by the codes "SEK" or "GBP"?
 SELECT currency
    FROM currencies
    WHERE code IN ('SEK', 'GBP');
Returns:

 CURRENCY
 Pounds Sterling
 Swedish Kronor

List all the formats other than those for identifiers 1, 5 and 7:
 SELECT format, format_id
    FROM formats
    WHERE format_id NOT IN (1, 7, 5);
Returns:

 FORMAT
FORMAT_ID
 Cassette
2
 DVD Audio
3
 Vinyl
4
 Audio CD
6
 Paperback
8
 DVD Video
9
 Video
10

BETWEEN and NOT BETWEEN

The operators BETWEEN and NOT BETWEEN are used to find values that are within or outside an interval. The interval includes the limits specified in the BETWEEN condition.

Find the EAN and release date for EANs outside the "Bookland" range (e.g. 978 prefix) that were released during January 1998:
 SELECT ean_code, release_date
    FROM items
    WHERE ean_code NOT BETWEEN 9780000000000 AND 9789999999999
    AND release_date BETWEEN DATE'1998-01-01' AND DATE'1998-01-31';
Returns:
EAN_CODE
 RELEASE_DATE
90431587720
 1998-01-05
93624662426
 1998-01-13
45778040629
 1998-01-20

BETWEEN may also be used for character comparisons. Strings are compared character by character from left to right.

For example:
 SELECT code, country
    FROM countries
    WHERE country BETWEEN 'South Africa' AND 'Suriname';
Returns:

 CODE
 CURRENCY
 ES
 Spain
 LK
 Sri Lanka
 SD
 Sudan
 SR
 Suriname
 ZA
 South Africa

Find which currencies have an exchange rate in the range of 1 to 2 to the euro:
 SELECT currency
    FROM currencies
    WHERE exchange_rate BETWEEN 1.00 AND 2.00;
Returns:

 CURRENCY
 Netherlands Antillian Guilders
 Australian Dollars
 Aruban Guilders
 Convertible Marka
 Barbados Dollars
 Leva
 ...


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX