CODEWITHSUNDEEP
mysql
user2360831
user2360831

Reputation: 367

Mysql from sum one table column subtract sum of another table column

Have 2 tables. In both tables exists columns for points and Id of user. Want to sum all points for particular user in one table, then to sum in another and subtract from total of one table, total of another table. Have 2 solutions (and also here http://sqlfiddle.com/#!9/f040a2/1 placed my testings). Please advice which is better for performance.

The first solution. Very clear and seems without "problems"

SELECT 
( SELECT SUM(`PurchasedPoints`) FROM `first_table` WHERE `Id` = ? ) 
- 
( SELECT SUM(`SpendPoints`) FROM `second_table` WHERE `Id` = ? ) AS `RemainingPaidPoints`

Another solution with LEFT JOIN. Somewhere read, that LEFT JOIN is better for performance than subqueries. But code looks complicated and not sure if it is reasonable to use it. 

SELECT SUM(`Ft`.`PurchasedPoints`) - `St`.`SpendPoints` AS `RemainingPaidPoints` 
FROM `first_table` `Ft` 

LEFT JOIN (
SELECT SUM(`SpendPoints`) AS `SpendPoints`, `Id` FROM `second_table` WHERE `Id` = ? 
GROUP BY `Id`
) `St` ON `Ft`.`Id` = `St`.`Id` 

WHERE `Ft`.`Id` = ? 

GROUP BY `Ft`.`Id`

With both get the same result, but... which is better for performance etc., please?

Upvotes: 1

Views: 1345

Answers (1)

omar jayed
omar jayed

Reputation: 868

JOIN works with tables, subqueries work with data sets. That's why JOIN is faster than a subquery.

However, in both the queries you are running two separate SELECT queries. So performance-wise both are same. And, if you are counting the summation of a particular id then you don't need to use GROUP BY.

SELECT SUM(`Ft`.`PurchasedPoints`) - SUM(`St`.`SpendPoints`) AS `RemainingPaidPoints` 
FROM `first_table` `Ft` 
LEFT JOIN `second_table` `St`
ON `Ft`.`Id` = `St`.`Id` 
WHERE `Ft`.`user_id` = ?

Upvotes: 1

Related Questions