Select inside a Select Mysql statment

Question

So I have 3 tables, db, db2, db3. I need all the rows from db, but also the total number in db2 and db3. I would like to do it in one query and the query below works..

SELECT *, (SELECT COUNT(*) FROM db2) AS total2, (SELECT COUNT(*) FROM db3) AS total3  
FROM db

What I am wondering is, since those nested SQL queries show up in every row, do they run that many time? or does mysql optimize such that it runs it once, makes it a constant, and adds it to every row?? Its important to know in case the db gets to be pretty big;

Answers and references to documentation on this would be much appreciated.

yes, they would. they MAY get cached by the db and effectively only run once, but there's better/easier ways of doing this. e.g. run the counts separately, then just insert their values into the main `select *` as you fetch the rows. — Marc B, Nov 16 '15 at 17:07

score 2 · Accepted Answer · edited May 23 '17 at 11:59

~~I'm not sure whether mysql caches or optimizes the subquery in your question but you can rewrite your query using a cross join to make sure the count queries only run once~~

select * from db
cross join (select count(*) as total2 from db2) t1
cross join (select count(*) as totla3 from db3) t2

Update

I checked the query plans and mysql already optimizes your original query because it recognizes that it's not a dependent subquery. Note that select_type is SUBQUERY, which runs only once, as opposed to DEPENDENT SUBQUERY, which would run once per row (see Difference between Subquery and Correlated Subquery)

Subquery

+----+-------------+-------+-------+---------------+-----------------------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key                   | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+-------+-------------+
|  1 | PRIMARY     | t2    | ALL   | NULL          | NULL                  | NULL    | NULL |   106 | NULL        |
|  2 | SUBQUERY    | t1    | index | NULL          | id                    | 4       | NULL | 38511 | Using index |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+-------+-------------+

Cross Join

+----+-------------+------------+--------+---------------+-----------------------+---------+------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key                   | key_len | ref  | rows  | Extra       |
+----+-------------+------------+--------+---------------+-----------------------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL                  | NULL    | NULL |     1 | NULL        |
|  1 | PRIMARY     | t2         | ALL    | NULL          | NULL                  | NULL    | NULL |   106 | NULL        |
|  2 | DERIVED     | t3         | index  | NULL          | id                    | 4       | NULL | 38511 | Using index |
+----+-------------+------------+--------+---------------+-----------------------+---------+------+-------+-------------+

@lonewarrior556 please see update, looks like the cross join is not necessary after all — FuzzyTree, Nov 16 '15 at 17:57
Thanks for being thorough, I still learned about cross joins and that they can be used. so I appreciate both answer. — lonewarrior556, Nov 17 '15 at 15:36

score 0 · Answer 2 · answered Nov 16 '15 at 17:18

So basically what you need to do is to join db2 and db3 into db. To do this you should first chose the things you need from db2 and db3 (I would not pick everything if it is not needed, it will just make the query longer/slower). Then joining db2 into db3 and db3 into db with all the information you need.

Select
  db2.total_number,
  db3.total_number,
  db.*
From
  db2 
Inner Join
  db3 On db2.some_equal_key = db3.some_equal_key
Inner Join
  db On db3.some_equal_key = db.some_equal_key

Please note that joining tables requires at least one same value (for example the same primary key). This will be used for the join statement - in my example its some_equal_key

A. what if they don't have shared keys? B. are you expecting db2.total_number to sum all the rows in a table? — lonewarrior556, Nov 17 '15 at 15:38

Select inside a Select Mysql statment

2 Answers2