StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's the better solution:</p> <pre><code>SELECT COUNT(*) FROM (SELECT `t2`.`name` FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` WHERE `t1`.`another_column` = 123 GROUP BY `t1`.`any_col`) tmp </code></pre> <p>You just need to wrap <code>SELECT COUNT(*)</code> around the query.</p> <p>The problem is that <code>COUNT()</code> changes from meaning "give me a count of the rows in the result set" to an aggregate function meaning "give me a count of the rows for each group that match my criteria" when used with the <code>GROUP BY</code> clause. In your case, since there are no rows for the group where another_column = 123, nothing is returned.</p> <p>If you wrap that in another <code>SELECT COUNT(*)</code>, you're again saying, "give me a count of the rows in the result set".</p> <p>In the first query, <code>COUNT()</code> is working with <code>GROUP BY</code>. But with no criteria specified, you're getting at least one row returned from table_1. Since there are no corresponding table_2 records, NULL evaluates to 0 for <code>COUNT()</code>. Don't use the first query. Though it returns what you're expecting, it's only by chance. For the query to be meaningful, you should specify criteria for t1.another_column in the <code>WHERE</code> clause.</p> <p>The second query works because you're asking for a row count of the result set. Since there are no rows where t1.another_column = 123, there are 0 rows returned, and <code>COUNT()</code> evaluates to 0.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload