How to reorganize this MySQL code?

Question

SELECT AVG(`col5`) FROM `table1` WHERE `id` NOT IN ( SELECT `id` FROM `table2` WHERE `col4` = 5 ) group by `col2` having sum(`col3`) > 0 UNION SELECT MAX(`col5`) FROM `table1` WHERE `id` NOT IN ( SELECT `id` FROM `table2` WHERE `col4` = 5 ) group by `col2` having sum(`col3`) = 0 

For reasons of readability and performance, I think this code could be reorganized. But how?

PUBLICATIONS

• external selection removed

• made the first choice to return the amount, and the second to return a different value

• replaced SUM with AVG

Answer 1

 SELECT * FROM table1 t1 left outer join table2 t2 on t1.id = t2.id and t2.col4 = 5 where t2.id is null group by t1.col2 having sum(col3) >= 0 

External selection is missing from the FROM and does not add anything, so I deleted it. NOT IN inefficient compared to the LEFT OUTER JOIN method, so I replaced it. Two UNION were easily combined into one using >= .

Update: Note the use of UNION ALL , not UNION . I don’t think you want to remove duplicates, and it will work faster this way.

 SELECT AVG(t1.col5) FROM table1 t1 left outer join table2 t2 on t1.id = t2.id and t2.col4 = 5 where t2.id is null group by t1.col2 having sum(t1.col3) > 0 UNION ALL SELECT MAX(t1.col5) FROM table1 t1 left outer join table2 t2 on t1.id = t2.id and t2.col4 = 5 where t2.id is null group by t1.col2 having sum(t1.col3) = 0 

Answer 2

 SELECT t1.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id = t2.id WHERE t2.col4 <> 5 AND SUM(t1.col3) > 0 GROUP BY t1.col2 

Answer 3

I assume you want this:

 SELECT * FROM `table` WHERE col2 IN (SELECT col2 FROM `table1` WHERE `id` NOT IN ( SELECT `id` FROM `table2` WHERE `col4` = 5 ) group by `col2` having sum(`col3`) >= 0 ) 

When using GROUP BY you should only return columns whose names are specified in the GROUP BY or that include the aggregate function. Therefore, the internal SELECT gets col2 values ​​here, where the sum is greater than or equal to zero, then the external SELECT captures the entire row for these values.

Answer 4

 SELECT IF(SUM(`table1`.`col3`) > 0, AVG(`table1`.`col5`), MAX(`table1`.`col5`)) FROM `table1` LEFT JOIN `table2` ON `table2`.`id` = `table1`.`id` AND `table2`.`col4` = 5 WHERE `table2`.`id` IS NULL GROUP BY `table1`.`col2` HAVING SUM(`table1`.`col3`) >= 0 

Also * considered harmful. If you want your query to be compatible with future changes in your database model, indicate the columns by their names.