By Michael Marr
Expert Author
Article Date: 2010-08-17
Staying in fashion is often a tough proposition. If you're one of the unfortunate ones that do attempt to keep up, you likely end up with an overpriced and overstocked wardrobe. Some fashions, however, never die, and thus can continue to be used at your discretion. Likewise, your database can become stuffed to the seams, and there may be situations where you want to return a result if it matches not only a single value, but also any from a host of values.
For instance, you might have a list of ids and you only want to return rows that have an id in that list. One approach is an endless string of conditions:SELECT * FROM t1 WHERE
t1.id = 1 OR t1.id = 3 OR t1.id = 5 OR t1.id = 7
OR t1.id = 11 OR t1.id = 13 OR t1.id = 17
.
.
.
Alternatively, we can use the INkeyword to produce a much cleaner SELECTstatement:SELECT * FROM t1 WHERE t1.id IN (1,3,5,7,11,13,17,19,23);
Additionally, your particular DBMS might also supply various functions regarding sets. In MySQL, we have FIND_IN_SET, MAKE_SET, and EXPORT_SET.FIND_IN_SETallows us to find the location of a particular item in a set:mysql>SELECT FIND_IN_SET(1, '1,3,5,7,11,13,17,19,23');
+-------------------------------------------+
| FIND_IN_SET(11, '1,3,5,7,11,13,17,19,23') |
+-------------------------------------------+
| 5 |
+-------------------------------------------+
1 row in set (0.00 sec)
The next two functions are a little different. They utilize bits, and thus can be slightly confusing at first glance. MAKE_SETtakes a list of strings and creates a set out of the specified bits:mysql>SELECT MAKE_SET(22, 'make', 'eat', 'fried', 'grilled', 'chicken', 'pork');
+--------------------------------------------------------------+
| MAKE_SET(22,'make','eat','fried','grilled','chicken','pork') |
+--------------------------------------------------------------+
| eat,fried,chicken |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
The reason 22 returns what it does is you have to map the strings to their appropriate position on a binary representation, i.e. 'make' is the first bit, 'eat' is the second, and so on. When you pass a number, it is converted to binary, and whatever bits are on, those strings are returned. Supplying 2 is equal to '01' in binary, so only the second bit is on, and thus 'eat' is returned. When we send 22, which is 01101 in binary, it returns the second, third, and fifth bits.EXPORT_SETdoes an auto-generation of a set. You supply the bits to turn on, the values for on and off, the separator, and the length to evaluate. For our example, we'll use 3, which is '11' in binary:mysql>SELECT EXPORT_SET(3, 'Y', 'N', ',', 7);
+---------------------------------+
| EXPORT_SET(3, 'Y', 'N', ',', 7) |
+---------------------------------+
| Y,Y,N,N,N,N,N |
+---------------------------------+
1 row in set (0.00 sec)
The later two functions discussed may be a little hard to implement, but certainly sets are IN fashion this season. Happy querying!
