Mysql count null values left join

In one of the previous articles I discussed performance of the three methods to implement an anti-join in MySQL. Just a quick reminder: an anti-join is an operation that returns all records from one table which share a value of a certain column with no records from another table. When inner.

Now, what if inner. Let's create some sample tables: Table creation details. Each of the tables contains 1,, random values from 1 to 99, and also some NULL values. There is an index on value in both tables. The query completes in 9. If the column is defined to allow NULL, there might be a difference between the number of values in email.

There are several possible intended meanings for the query, and there are several different ways to write the query to express those meanings more clearly. But the original query is ambiguous, for a few reasons. And everyone who reads this query afterwards will end up guessing what the original author meant. Show 1 more comment. Active Oldest Votes. Improve this answer. Add a comment. You put count 1 , change count f. Cristina Carrasco Cristina Carrasco 5 5 silver badges 15 15 bronze badges.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. The problem is that when we JOIN the two tables, the results will only include users who have posts. A better way to do this as suggested by Tom Davies is instead of counting all records, only count post ids:.

This is actually I trick I learned from the real Jen while at Automattic :. Now instead of Simon user id 2 having 2 posts, he has 4 — what happened? Again, we can look at the un-aggregated results:.

The problem is that each post gets joined with each page result. Simon has 2 posts, each of which gets joined with the 2 pages, so when we use COUNT it sees 4 results and returns that amount. To understand how it works, lets focus on counting posts. The first subquery counts how many posts each user has if they have any posts:. For the users with posts, the result has the post count.

For the users without posts, the result is NULL. Somewhat complicated, but the only way I know how to do it. If you know of a better way, please drop a comment below. By counting distinct post ids and page ids, we avoid counting NULLs and also avoid counting duplicates due to joining on posts and pages. To test performance differences, I loaded the tables with 16, posts and nearly 25, pages.