Selecting Specific Rows
Rows are selected in the
SELECTstatement according to the search condition in the
WHEREclause. This condition relates column value(s) to expressions.
Comparison Conditions and WHERE
Comparison operators that may be used in the
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
OR, and reversed using
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 Latin1 character set as defined by ISO 8859-1, see the Mimer SQL Reference Manual, Appendix B, Character Sets for the exact sequence. Default collation for Unicode characters (national character data) is the UCS_BASIC collation.
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;
Comparing Temporal Data
When stating conditions on temporal data in tables, datetime and interval literals can be specified. There are also the
BUILTIN.UTC_TIMESTAMPfunctionality 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 September 5, 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.
LIKEis used to search for character strings that match a specified pattern.
Patterns in the
LIKEcondition are 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
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 SQL statements are always written within single quotation marks (' ').
LIKEpredicate 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
LIKEcomparison are not.
For example:'Dollars ' = 'Dollars' is true 'Dollars ' LIKE 'Dollars ' is true 'Dollars ' LIKE 'Dollars%' is true
but'Dollars ' LIKE 'Dollars' is false
LIKEpredicate may include an
ESCAPEclause 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:
matches any string that contains an upper-case `P'. matches any string that contains an upper or lower 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
INfinds the values that are contained in a set of values. The set is given as a comma-separated list enclosed in parentheses.
NOT INfinds 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');
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, 5, 7);
Note: NOT IN is undefined if the subselect's result contains a NULL value. E.g. SELECT * FROM tab WHERE 1 NOT IN (3, <null>, 4) will return an empty result set.
BETWEEN and NOT BETWEEN
NOT BETWEENare used to find values that are within or outside an interval. The interval includes the limits specified in the
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';
BETWEENmay also be used for character comparisons.
For example:SELECT code, country FROM countries WHERE country BETWEEN 'South Africa' AND 'Suriname';
Find which currencies have an exchange rate in the range of 1 to 2:SELECT currency FROM currencies WHERE exchange_rate BETWEEN 1.00 AND 2.00;
SYMMETRICoption which is used to verify the interval's lower and upper limits in both directions. This is especially useful when writing queries where the
BETWEENlimit values are not defined until run-time, or where the limits are column or function references.
BETWEEN SYMMETRIC example with host variables:SELECT code, country FROM countries WHERE country BETWEEN SYMMETRIC :country1 AND :country2; country1: Suriname country2: South Africa
SYMMETRICspecified, this query would have returned an empty result set.
BETWEEN SYMMETRIC example with columns and a function:SELECT * FROM table1, table2 WHERE table1.col1 BETWEEN SYMMETRIC table2.col1 AND func(table1.col3);
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40