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:
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 exampleWHERE price >= 10.00 AND price <= 20.00
may not be expressed asWHERE 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 ASCII 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, chapter 6, 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;
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';
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;
For an example of interval literals, see Datetime Arithmetic and Functions.
LIKE is used to search for character strings that match a specified pattern.
Patterns in the LIKE condition can be written with wildcard characters (also called meta-characters):
_ (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%';
Find all formats whose names do not contain the string "Audio":SELECT format FROM formats WHERE format NOT LIKE '%Audio%';
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, Chapter 8, The LIKE Predicate, for more details.
More about Searching for Character Strings
Some other examples of searching for character strings are:
matches any string that contains an upper-case 'P'. matches any four letter character string ending with lower-case 'abc'. matches any string ending with 'A%'. matches any four letter string with D and d in the first and third positions respectively. Examples of possible values: Dude, Dads.
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" and "GBP"?SELECT currency FROM currencies WHERE code IN ('SEK', 'GBP');
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;
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);
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';
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';
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40