Counting number of joined rows in left join

sql

join

best-practices

performance

byAnton Shumikhin·Nov 18, 2024

To count rows in a LEFT JOIN, use COUNT on a unique column from the joined table, and then group by a primary table identifier. Here is an SQL example:

-- On your marks, get set, go. Let's count some posts!
SELECT u.id, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

This SQL query efficiently counts posts per user, including 0 counts for users without any posts. Remember: count using the joined table's unique field (p.id); group by the primary table's unique field (u.id) to get meaningful results.

Consider using COALESCE and SUM (CASE)

The COALESCE function comes in handy when dealing with NULL counts, replacing them with 0. Here's how to apply this function:

-- Here we go, turning Null into a perfect zero!
SELECT u.id, COALESCE(COUNT(p.id), 0) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Want to have a condition-based count? In such cases, SUM(CASE) is your guardian angel. It allows you to count based on your criteria:

-- Count only published, because drafts are just wishful thinking!
SELECT u.id, SUM(CASE WHEN p.published = 'true' THEN 1 ELSE 0 END) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Tactics for complex join scenarios

Optimize performance with Pre-Aggregation

When dealing with large datasets in joins, it can be beneficial to run pre-aggregation on counts in a subquery before the join:

-- Because size does matter when it comes to performance!
SELECT u.id, COALESCE(p.post_count, 0)
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS post_count
    FROM posts
    GROUP BY user_id
    /* Who's counting? SQL is, behind the scenes. */
) p ON u.id = p.user_id;

Duplicate count avoidance

When you need to avoid counting duplicate entries in joined rows, COUNT(DISTINCT) is your best bet:

-- Unique counts only, because we're not fans of repetition!
SELECT u.id, COUNT(DISTINCT p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Primary keys in SELECT statement

Include primary keys when using the SELECT statement in grouping. This ensures no guesswork goes into your results:

-- Always invite the primary key to your SQL party!
SELECT u.id, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Crafting solid SQL joins

While writing joins, you should:

Define the relation between the two tables clearly with the ON clause.
Use aliases for tables to simplify your SQL queries: u for users and p for posts is much more readable.
Be cautious about the placement of conditions. The positioning in the WHERE clause affects the accuracy of joined tables.

Be aware of pitfalls in SQL joins

When joining tables, ensure:

NULL matches are handled properly. Remember, LEFT JOIN includes all records from the left table, whether or not they match any records in the right table.
Non-standard join conditions (known as non-equijoin) should be used with care as they may yield unexpected, misleading results.
Avoid over-joining, i.e joining more tables than required. This could lead to performance degradation and complexities in handling the result set.