Barry brought an interesting point to me today. He needed to find if there existed any values for a particular set. For example this was his test dataset:
CREATE TABLE test (
id INT UNSIGNED NOT NULL,
foo INT UNSIGNED
);
INSERT INTO test (id, foo)
VALUES
(1, 1),
(1, 2),
(1, NULL),
(3, 4),
(3, 2),
(3, 3),
(4, 3),
(4, NULL),
(4, 5),
(5, 1),
(5, 1),
(5, NULL);
In this example he wanted to return all ids and if that id set contained any foos with a value of 1 or 2.
After bandying about for a bit he discovered that you can actually apply a limiter within an aggregate function.
So to find out which ids had a corresponding foos of 1 or 2 could be found from the following:
mysql> SELECT id, MAX(foo IN (1,2)) FROM test GROUP BY id;
+----+-------------------+
| id | MAX(foo IN (1,2)) |
+----+-------------------+
| 1 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
+----+-------------------+
4 rows in set (0.00 sec)
You could also apply a SUM function to determine how many results you actually had that matched your criteria.
January 13th, 2009 | Programming
Thanks for writing. You are correct that you can use an IN clause to determine if a value is in a set, but if you look closely you’ll notice that this technique actually returns all distinct entries, and indicates the aggregate numer of times it appears with the subsequent values defined in the MAX(.. IN ()) clause. This is great if you want to get the entire list of entries back, but summarized by the occurrence of a second value defined in the IN statement even if it is not in the set.
Comment by Erik — December 7, 2011 @ 1:31 pm