How the MAKE_SET() Function Works in MySQL

In MySQL, the MAKE_SET() function returns a set value (a string containing substrings separated by , characters) consisting of the characters specified as arguments when you call the function.

When you call the function, you specify any number of strings (separated by a comma), as well as one or more bit values that determine which strings to return in the set value.

Syntax

The syntax goes like this:

MAKE_SET(bits,str1,str2,...)

Example

Here’s a basic example:

SELECT MAKE_SET(1, 'a','b','c','d') Result;

Result:

+--------+
| Result |
+--------+
| a      |
+--------+

This result may seem obvious at first. After all, our first argument is 1, and the function returns the first string.

However, that’s not quite how it works.

Here’s another example.

SELECT MAKE_SET(4, 'a','b','c','d') Result;

Result:

+--------+
| Result |
+--------+
| c      |
+--------+

Notice it returned the third string, even though we specified 4 as the first argument?

This is because the MAKE_SET() function uses the binary representation of the first argument to return the applicable strings in the subsequent arguments.

Look at the following code example to see what I mean:

SELECT 
  BIN(1) AS '1',
  BIN(2) AS '2',
  BIN(3) AS '3',
  BIN(4) AS '4',
  BIN(5) AS '5',
  BIN(6) AS '6',
  BIN(7) AS '7',
  BIN(8) AS '8',
  BIN(9) AS '9',
  BIN(10) AS '10';

Result:

+------+------+------+------+------+------+------+------+------+------+
| 1    | 2    | 3    | 4    | 5    | 6    | 7    | 8    | 9    | 10   |
+------+------+------+------+------+------+------+------+------+------+
| 1    | 10   | 11   | 100  | 101  | 110  | 111  | 1000 | 1001 | 1010 |
+------+------+------+------+------+------+------+------+------+------+

Here, I use the BIN() function to return each number’s binary value.

We can see that the binary representation of 4 is 100. We need to visualize this backwards in order to apply it to our MAKE_SET() example above. In our case, this is a three-digit binary value, with the right-most digit corresponding to the first string, the next digit corresponds to the second string, and the leftmost digit corresponds with the third string.

In binary terms, 1 is “on” and 0 is “off”. The MAKE_SET() function only returns strings that have a corresponding 1 in their binary value. Therefore, our example above returns the third string.

Here’s another example using a different value:

SELECT MAKE_SET(10, 'a','b','c','d') Result;

Result:

+--------+
| Result |
+--------+
| b,d    |
+--------+

In this case, the binary value is 1010. It therefore has two 1s, which correspond to the second and fourth string arguments.

Here are some more examples to demonstrate the concept further:

SELECT 
  MAKE_SET(1, 'a','b','c','d') AS '1',
  MAKE_SET(2, 'a','b','c','d') AS '2',
  MAKE_SET(3, 'a','b','c','d') AS '3',
  MAKE_SET(4, 'a','b','c','d') AS '4',
  MAKE_SET(5, 'a','b','c','d') AS '5',
  MAKE_SET(6, 'a','b','c','d') AS '6',
  MAKE_SET(7, 'a','b','c','d') AS '7',
  MAKE_SET(8, 'a','b','c','d') AS '8',
  MAKE_SET(9, 'a','b','c','d') AS '9',
  MAKE_SET(10, 'a','b','c','d') AS '10';

Result:

+---+---+-----+---+-----+-----+-------+---+-----+-----+
| 1 | 2 | 3   | 4 | 5   | 6   | 7     | 8 | 9   | 10  |
+---+---+-----+---+-----+-----+-------+---+-----+-----+
| a | b | a,b | c | a,c | b,c | a,b,c | d | a,d | b,d |
+---+---+-----+---+-----+-----+-------+---+-----+-----+

And here’s an example using a different set of strings:

SELECT MAKE_SET(5, 'Cat','Dog','Horse','Duck') Result;

Result:

+-----------+
| Result    |
+-----------+
| Cat,Horse |
+-----------+

Multiple Binary Values

You can use a pipe in order to pass multiple binary values in the first argument:

SELECT MAKE_SET(1 | 4, 'a','b','c','d') Result;

Result:

+--------+
| Result |
+--------+
| a,c    |
+--------+

Note that you’ll get the same result if you reverse the binary values in the first argument:

SELECT MAKE_SET(4 | 1, 'a','b','c','d') Result;

Result:

+--------+
| Result |
+--------+
| a,c    |
+--------+

NULL Values

Any strings with NULL values are not appended to the result.

Example:

SELECT MAKE_SET(1 | 4, 'a','b',NULL,'d') Result;

Result:

+--------+
| Result |
+--------+
| a      |
+--------+