MySQL optimize a query COUNT-ing DISTINCT values or NULL IF NO ROWS
<p>I need to get <strong>the count of distinct athletes</strong> from a <strong>times table</strong> where <strong>time is less than some-other-time</strong>. But the tricky part is that <strong>if there are no times at all</strong> (to compare against, <strong>I need to get NULL in return</strong>).</p>

<p>Let me give you an example:</p>

<pre><code>CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO teams 
VALUES (NULL, 'Texas'), (NULL,'Oklahoma');

mysql&gt; select * from teams;
+----+----------+
| id | name     |
+----+----------+
|  1 | Texas    |
|  2 | Oklahoma |
+----+----------+


CREATE TABLE `times` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `time` decimal(8,2) NOT NULL,
  `athlete` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `team_id` (`team_id`)
);

INSERT INTO times VALUES 
  (NULL, 1, 19.10, 'Dave'),
  (NULL, 1, 19.09, 'Dave'),
  (NULL, 1, 19.07, 'Dave'),
  (NULL, 1, 19.56, 'John'),
  (NULL, 1, 19.60, 'John'),
  (NULL, 1, 19.75, 'John');

mysql&gt; select * from times;
+----+---------+-------+---------+
| id | team_id | time  | athlete |
+----+---------+-------+---------+
|  1 |       1 | 19.10 | Dave    |
|  2 |       1 | 19.09 | Dave    |
|  3 |       1 | 19.07 | Dave    |
|  4 |       1 | 19.56 | John    |
|  5 |       1 | 19.60 | John    |
|  6 |       1 | 19.75 | John    |
+----+---------+-------+---------+
</code></pre>

<p>So far we have two teams. <code>Team 1</code> has <strong>two athletes</strong> with <strong>6 times</strong> and <code>Team 2</code> has no athletes (and times respectively).</p>

<p>If I want to know <code>How many Athletes from Texas are faster than 19.50</code> I can do:</p>

<pre><code>SELECT COUNT(DISTINCT athlete) FROM times WHERE time &lt; 19.50 and team_id = 1;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       1 |
+-------------------------+
</code></pre>

<p>which is correct. </p>

<p>And if I want to check <code>How many Athletes from Texas are faster than 19.00</code></p>

<pre><code>SELECT COUNT(DISTINCT athlete) FROM times WHERE time &lt; 19.00 and team_id = 1;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       0 |
+-------------------------+
</code></pre>

<p>is also correct (because we have <strong>two athletes</strong> from <code>Texas</code>).</p>

<p>But if I want to check: <code>How many Athletes from Oklahoma are faster than 19.00</code></p>

<pre><code>SELECT COUNT(DISTINCT athlete) FROM times WHERE time &lt; 19.00 and team_id = 2;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       0 |
+-------------------------+
</code></pre>

<p>is <strong>not correct</strong> because we have <strong>no times</strong> from <code>Oklahoma</code>. So here I need to get <code>NULL</code> in return.</p>

<p>I managed to find a solution with a sub-query:</p>

<pre><code>SELECT
  IF(
    EXISTS(
      SELECT 1 FROM times WHERE team_id = 2
    ),
    COUNT(DISTINCT athlete),
    NULL
  ) as count
FROM `times`
WHERE
  team_id = 2 AND time &lt; 19.00;
+-------+
| count |
+-------+
|  NULL |
+-------+
</code></pre>

<p>which is correct and if I test it for <code>Texas</code>, I get:</p>

<pre><code>SELECT 
  IF(
    EXISTS(
      SELECT 1 FROM times WHERE team_id = 1
    ),
    COUNT(DISTINCT athlete),
    NULL
  ) as count
FROM `times`
WHERE
  team_id = 1 AND time &lt; 19.00;

+-------+
| count |
+-------+
|     0 |
+-------+
</code></pre>

<p>It returns the correct answer.</p>

<p>But the problem is that I used a <code>sub-query</code> which <strong>have to mimic</strong> all the filters of the main query except for the <code>time &lt; 19.00</code>. In my real application there are a lot more filters and I'm searching for a solution without a sub-query.</p>

<p>One thing that came to mind was to use SUM(CASE)</p>

<pre><code>SELECT SUM(CASE 
  WHEN time &lt; 19.50 
    THEN 1 
    ELSE 0 
  END) as count
FROM `times`
WHERE team_id = 2;

+-------+
| count |
+-------+
|  NULL |
+-------+
</code></pre>

<p>The problem is that this is <strong>counting-times not distinct athletes</strong>, thus for <code>Texas</code> I will get the wrong count;</p>

<pre><code>SELECT SUM(CASE 
  WHEN time &lt; 19.50 
    THEN 1 
    ELSE 0 
  END) as count
FROM `times`
WHERE team_id = 1;

+-------+
| count |
+-------+
|     3 |
+-------+
</code></pre>

<p>I get <strong>3 times</strong> faster that <code>19.50</code> instead of <strong>1 athlete</strong> faster than <code>19.50</code>.</p>