SELECT cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM ( SELECT substr(c_phone, 1, 2) AS cntrycode, c_acctbal FROM customer WHERE substr(c_phone, 1, 2) IN ('32', '27', '15', '26', '33', '17', '34') AND c_acctbal > ( SELECT avg(c_acctbal) FROM customer WHERE c_acctbal > 0.00 AND substr(c_phone, 1, 2) IN ('32', '27', '15', '26', '33', '17', '34') ) AND NOT EXISTS ( SELECT * FROM orders WHERE o_custkey = c_custkey ) ) AS vip GROUP BY cntrycode ORDER BY cntrycode; WHERE o_custkey = c_custkey ) ) AS vip GROUP BY cntrycode ORDER BY cntrycode; +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ | 1 | PRIMARY | customer | ALL | c_acctbal,i_c_acctbal_nationkey | NULL | NULL | NULL | 133653 | 100.00 | Using where; Using temporary; Using filesort | | 4 | DEPENDENT SUBQUERY | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3sf1.customer.c_custkey | 7 | 100.00 | Using index | | 3 | SUBQUERY | customer | ALL | c_acctbal,i_c_acctbal_nationkey | NULL | NULL | NULL | 133653 | 50.00 | Using where | +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ 3 rows in set, 2 warnings (0.01 sec) MariaDB [dbt3sf1]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1276 Message: Field or reference 'dbt3sf1.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 *************************** 2. row *************************** Level: Note Code: 1003 Message: select substr(`dbt3sf1`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3sf1`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3sf1`.`customer` where ((substr(`dbt3sf1`.`customer`.`c_phone`,1,2) in ('32','27','15','26','33','17','34')) and (`dbt3sf1`.`customer`.`c_acctbal` > (select avg(`dbt3sf1`.`customer`.`c_acctbal`) from `dbt3sf1`.`customer` where ((`dbt3sf1`.`customer`.`c_acctbal` > 0.00) and (substr(`dbt3sf1`.`customer`.`c_phone`,1,2) in ('32','27','15','26','33','17','34'))))) and (not((1,exists(select 1 from `dbt3sf1`.`orders` where (`dbt3sf1`.`orders`.`o_custkey` = `dbt3sf1`.`customer`.`c_custkey`)))))) group by substr(`dbt3sf1`.`customer`.`c_phone`,1,2) order by substr(`dbt3sf1`.`customer`.`c_phone`,1,2) 2 rows in set (0.00 sec) MariaDB [dbt3sf1]> set optimizer_switch='exists_to_in=off'; Query OK, 0 rows affected (0.00 sec) MariaDB [dbt3sf1]> explain extended SELECT cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM ( SELECT substr(c_phone, 1, 2) AS cntrycode, c_acctbal FROM customer WHERE substr(c_phone, 1, 2) IN ('32', '27', '15', '26', '33', '17', '34') AND c_acctbal > ( SELECT avg(c_acctbal) FROM customer WHERE c_acctbal > 0.00 AND substr(c_phone, 1, 2) IN ('32', '27', '15', '26', '33', '17', '34') ) AND NOT EXISTS ( SELECT * FROM orders WHERE o_custkey = c_custkey ) ) AS vip GROUP BY cntrycode ORDER BY cntrycode; +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ | 1 | PRIMARY | customer | ALL | c_acctbal,i_c_acctbal_nationkey | NULL | NULL | NULL | 133653 | 100.00 | Using where; Using temporary; Using filesort | | 4 | DEPENDENT SUBQUERY | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3sf1.customer.c_custkey | 7 | 100.00 | Using index | | 3 | SUBQUERY | customer | ALL | c_acctbal,i_c_acctbal_nationkey | NULL | NULL | NULL | 133653 | 50.00 | Using where | +------+--------------------+----------+------+---------------------------------+-------------+---------+----------------------------+--------+----------+----------------------------------------------+ 3 rows in set, 2 warnings (0.00 sec) MariaDB [dbt3sf1]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1276 Message: Field or reference 'dbt3sf1.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 *************************** 2. row *************************** Level: Note Code: 1003 Message: select substr(`dbt3sf1`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3sf1`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3sf1`.`customer` where ((substr(`dbt3sf1`.`customer`.`c_phone`,1,2) in ('32','27','15','26','33','17','34')) and (`dbt3sf1`.`customer`.`c_acctbal` > (select avg(`dbt3sf1`.`customer`.`c_acctbal`) from `dbt3sf1`.`customer` where ((`dbt3sf1`.`customer`.`c_acctbal` > 0.00) and (substr(`dbt3sf1`.`customer`.`c_phone`,1,2) in ('32','27','15','26','33','17','34'))))) and (not(exists(select 1 from `dbt3sf1`.`orders` where (`dbt3sf1`.`orders`.`o_custkey` = `dbt3sf1`.`customer`.`c_custkey`))))) group by substr(`dbt3sf1`.`customer`.`c_phone`,1,2) order by substr(`dbt3sf1`.`customer`.`c_phone`,1,2) 2 rows in set (0.00 sec)