Great question—you’ve already got a solid grasp of how these two approaches work, so let’s refine your judgment and add some context to make it more precise.

First, let’s recap the two queries you’re comparing, along with their execution behavior (your initial understanding is exactly right):

INNER JOIN写法:

SELECT * 
FROM a 
INNER JOIN ( 
  SELECT b.id, Count(*) AS Count 
  FROM b 
  GROUP BY b.id 
) AS b ON b.id = a.id;

This runs the subquery once upfront: it groups all rows in b by id to compute counts, creates a temporary result set, then joins it to a on matching ids.

LATERAL写法:

SELECT * 
FROM a, LATERAL ( 
  SELECT Count(*) AS Count 
  FROM b 
  WHERE a.id = b.id 
) AS b;

This runs the subquery once per row in a: for each row in a, it filters b to only rows where b.id = a.id and counts them.

Your Judgment: Mostly Correct, With Key Nuances #

Let’s break down your scenarios to clarify when each approach shines:

1. When a single row in a maps to multiple rows in b (your "subquery results multi-row to main table single-row" scenario)

You’re absolutely right that INNER JOIN is almost always more efficient here.
Running a single grouping operation on b is far less overhead than running a separate count query for every row in a—especially if a has a large number of rows. Even with indexes on b.id, repeating the count logic hundreds or thousands of times adds up quickly. The INNER JOIN approach minimizes I/O and computation by reusing a single precomputed result set.

2. 1:1 mapping (one row in a maps to zero or one row in b)

Your call that LATERAL might be better here isn’t wrong, but it’s not an absolute rule. Performance depends on a few factors:

• Indexing: If b.id has a unique index (which makes sense for a 1:1 mapping), the LATERAL subquery will be a fast index lookup per row. For small a tables, this overhead is negligible.
• Optimizer behavior: Many modern databases (like PostgreSQL) can optimize LATERAL subqueries in 1:1 scenarios to behave like a regular JOIN under the hood, so the performance gap might disappear entirely.
• Flexibility: LATERAL truly shines if you need to reference multiple columns from a in the subquery (not just id), which isn’t possible with a standalone subquery in an INNER JOIN. But purely for count efficiency in 1:1 cases, it’s often a toss-up unless a is extremely small.

Extra Context to Consider #

• Size of a: If a has only a handful of rows, the overhead of running LATERAL subqueries multiple times is trivial—you might not notice a difference either way. But if a has millions of rows, INNER JOIN will outperform LATERAL by a wide margin in most cases.
• Aggregation complexity: If your subquery does more than a simple COUNT(*) (e.g., complex filters or multiple aggregations), the cost of repeating it per row in LATERAL becomes even more pronounced.

Final Takeaway #

Your core intuition is correct: precomputing aggregations with INNER JOIN is better when dealing with multi-row mappings from b to a, and LATERAL can be more efficient (or at least equally performant) in 1:1 scenarios—especially when you need the flexibility to reference main table columns in the subquery. Always test with your actual data and check the execution plan if you’re unsure!

内容的提问来源于stack exchange，提问作者Yaroslav Malyk