Operators for Working with Sets - MySQL

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:

• The IN operator returns 1 if a match is found (as in the first and third examples) and 0 if no match is found (as in the second and fourth examples).
  
• You can use any datatypes for the value to be found and for the values in the list to be searched, and you can mix datatypes in the list.
  
• When trying to match a string, comparisons are case-insensitive, unless you use the BINARY qualifier. (Compare the first two examples.)
  
• MySQL will attempt to perform a type conversion between strings and numerals, and vice versa. Hence, the number 5 is matched by the string '5' in the fourth example (but see the rules in the following “Type Conversions in Comparisons” section).

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
FROM members
WHERE firstname IN ('Bill', 'Mary', 'George');

produces the same result as this one:

SELECT lastname, dob
FROM members
WHERE firstname='Bill' OR firstname='Mary' OR firstname='George';

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,
MySQL will signal an error.

---

NOTE  Subqueries were introduced in MySQL 4.1.We’ll discuss them in more detail in Chapter 8.

---

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
member whose first name is Bill? Yes, there is someone in the members list whose first name is Bill.”

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

---

CAUTION  Through MySQL 5.0.0, it is not possible to use a LIMIT clause in a subquery following an IN, ANY, or SOME clause.

---

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.

---

NOTE  The order in which arguments are passed to GREATEST() and LEAST() has no effect on the result.

---

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.

---

CAUTION  In older versions of MySQL (prior to 3.23), it was possible to use MAX() and MIN() as synonyms for GREATEST() and LEAST(), and you may see this usage in older references. This is no longer the case, and attempting to use one of these functions on a set will cause an error.

---

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.