MySQL多表关联求和查询优化：是否有替代子查询的更佳方案？

Hey there! Your current query works perfectly well for getting the totals you need, but it’s far from the only solution—and there are actually cleaner, more efficient ways to structure this depending on your specific scenario. Let’s break down some options, plus talk about why they might be a better fit.

First: Let’s Recap Your Original Query #

Just to set the baseline, here’s your existing code (formatted for clarity):

SELECT 
  SUM(r.total_value) 'Total Value',
  (SELECT SUM(p.payment) 
   FROM payments p 
   INNER JOIN rents r ON p.rents_id = r.id 
   WHERE r.events_id=8) 'Total Payment'
FROM rents r 
WHERE r.events_id=8 ;

This gets the job done, but the subquery for Total Payment is re-joining the rents table from scratch, which is a bit redundant. Let’s fix that.

Alternative 1: Two Independent Aggregated Subqueries (Clean & Straightforward) #

This approach splits each total into its own focused subquery, then combines them with a CROSS JOIN (since both subqueries return a single row). It’s super readable and avoids redundant work:

SELECT 
  rent_summary.total_value AS 'Total Value',
  payment_summary.total_payment AS 'Total Payment'
FROM
  -- Calculate total rent value for the event
  (SELECT SUM(total_value) AS total_value 
   FROM rents 
   WHERE events_id = 8) AS rent_summary
CROSS JOIN
  -- Calculate total payments linked to the event's rents
  (SELECT SUM(p.payment) AS total_payment 
   FROM payments p
   JOIN rents r ON p.rents_id = r.id
   WHERE r.events_id = 8) AS payment_summary;

Why this is better: #

• Each subquery has a single, clear purpose—easy to debug or modify later.
• If you need to adjust one total (like adding a filter to payments), you only touch one part of the query.

Alternative 2: Pre-Aggregate Payments First (Avoid Duplication Risks) #

A common pitfall with one-to-many joins is accidentally multiplying aggregate values (e.g., counting a rent’s total_value once per linked payment). Pre-aggregating payments at the rents_id level first avoids this, and handles cases where some rents have no payments:

SELECT
  SUM(r.total_value) AS 'Total Value',
  COALESCE(SUM(p.payment_sum), 0) AS 'Total Payment' -- COALESCE returns 0 if no payments exist
FROM rents r
LEFT JOIN (
  -- Sum payments per rent first to avoid duplication
  SELECT rents_id, SUM(payment) AS payment_sum
  FROM payments
  GROUP BY rents_id
) p ON r.id = p.rents_id
WHERE r.events_id = 8;

Why this is better: #

• By grouping payments first, each rent record is only joined once—no risk of inflating the total_value sum.
• LEFT JOIN + COALESCE ensures you get a valid number (0) instead of NULL if there are no payments for the event.

Alternative 3: Simplify Your Original Subquery #

If you want to stick close to your original structure but cut redundancy, rewrite the subquery to reference the event’s rent IDs directly instead of re-joining the entire rents table:

SELECT
  SUM(r.total_value) AS 'Total Value',
  (SELECT SUM(p.payment)
   FROM payments p
   WHERE p.rents_id IN (SELECT id FROM rents WHERE events_id = 8)) AS 'Total Payment'
FROM rents r
WHERE r.events_id = 8;

For even better performance (especially with indexes on rents.id and payments.rents_id), use an EXISTS clause:

SELECT
  SUM(r.total_value) AS 'Total Value',
  (SELECT SUM(p.payment)
   FROM payments p
   WHERE EXISTS (
     SELECT 1 FROM rents r_inner 
     WHERE r_inner.id = p.rents_id 
       AND r_inner.events_id = 8
   )) AS 'Total Payment'
FROM rents r
WHERE r.events_id = 8;

Which One Should You Choose? #

• If readability and maintainability are top priorities: Go with Alternative 1.
• If you need to handle edge cases like rents with no payments, or want to avoid aggregation bugs: Alternative 2 is the safest bet.
• If you prefer minimal changes to your original query: Alternative 3 cleans up redundancy without a full rewrite.

All these approaches are valid and performant—your choice depends on your specific data patterns and needs.

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