MySQL also has several operators and functions for use with sets of values. The IN operator answers the question, “Does value N match at least one of the values in the set A, B, C, D…?” Here are some examples:
You should note the following points when using the IN operator:
IN can be used in place of (and is basically shorthand for) multiple OR operators in a WHERE clause. For example, this query:
SELECT lastname, dob
produces the same result as this one:
SELECT lastname, dob
The IN operator can also be extremely useful when used with subqueries in MySQL 4.1 and above. Let’s return for a moment to our members table from the “Replacing Program Logic with SQL Logic: A Demonstration” section earlier in this chapter. Suppose that now we would like to know if any members were born in the year 1961. If you’re a programmer, you might have visions of writing some sort of for or while loop that iterates over the dates extracted from a query. Or you might be thinking that you could use something like this to accomplish this task:
SELECT COUNT(*) FROM members WHERE YEAR(dob)='1961';
You could do that, but you would be getting your original question answered only in an indirect fashion; you would still need to test the result to see whether the result was greater than zero. By using IN and a subquery, however, you can obtain a Yes or No (actually 1 or 0) answer:
SELECT '1961' IN (SELECT YEAR(dob) FROM members);
The inner SELECT returns a list of years, and the outer SELECT looks for a match in that list. By the way, the inner SELECT must return a single column; otherwise,
In place of IN, you can also use a comparison operator followed by either of the keywords ANY or SOME.
We show an equivalent using the COUNT() function and a comparison operator in the same example. This example can be thought of as saying, “Is there some
The following query and result can be thought of as saying, “There is not any member whose first name is Andy, correct? Yes, that’s correct.”
You can use any of the comparison operators =, !=, <>, <, <=, >, and >= with ANY or SOME. (You cannot use <=>.) Here’s an example:
This is equivalent to asking the question, “Do we have any members who were born in the year 1922 or earlier?” and getting the answer, “No.”
Finally, you can find the greatest and least values in a set by using GREATEST() and LEAST(). They’re used in a similar fashion: each accepts a comma-delimited set of values and returns the largest or smallest value from the set. The following is an example of using both of these functions.
Note that since we’ve mixed integer and decimal values, the result is expressed as a decimal with a precision equal to that of the argument with the greatest number of decimals.
You can also use the GREATEST() and LEAST() functions with strings:
Both functions are case-insensitive. Although you can use the BINARY modifier with either one of them without generating any errors, doing so has no effect on the result.
It’s possible to use both numeric and string arguments in one call to GREATEST() or LEAST(), but all of the strings are converted to 0.
Finally, there are two functions for working with sets of values: ELT() and FIELD(), which complement one another. ELT() is used to retrieve the value of the element at the specified index within a set. FIELD() finds the position or index of a given value within a set. Each takes as parameters a set of values, all of which except the first make up a list to be tested.
For ELT(), the first element in the set is the index of the value to be retrieved.
The first argument used with ELT() must be an integer; if it is less than 1 or greater than the number of elements in the list, the function returns NULL. Note that using 1 for the first argument will return the first element in the list that follows it. You can think of this function as working like an array index does in most programming languages.
The FIELD() function attempts to match the first argument in the list that follows it. Here, too, the numbering of the list starts with 1.
Note that these two functions are usually employed with lists of strings, but it’s also possible to use them with numbers, as shown in the next example.
As you can likely deduce from these examples, the value used for the first argument to ELT() can be a string, as long as that string contains only digits, and the first argument to FIELD() can be a number.
blog comments powered by Disqus