sql - MySQL: Count results of subquery and still use the set it returns -

the question how find total number of rows inner query returned, still use set retuned inner query (without repeating same query twice).

i have query:

select a1.name name (     select b1.id id     b1 join b2 join b3 ...     b1.x = ... , ... ) ids join a1 on a1.id = ids.id join a2 on a2.id = ids.id , ... join a3 on a3.id = ids.id , ... a1.name = 'apple' , a2.name = 'orange' , a3.name = 'pear' union select same query 'cat', 'dog', 'mouse' union select same query 'python', 'perl', 'java' order name 

with number of tables , complicated conditions, set of ids selected in subquery.

i want know 2 different things:

• the values selected large query, uses set of specific ids
• the number of ids selected inner subquery: how many ids considered, if not matched in outer query.

the tables large, question not how find info, how efficiently.

say, executing inner query twice, once subquery , once counting, slow.

can find both types of info in 1 query?

the order of names independent ids, guess cannot group a1.id cannot use count distinct a1.id.

this long comment, , doesn't answer questions (which don't understand). but, why using union? have single where clause conditions:

where (a1.name = 'apple' , a2.name = 'orange' , a3.name = 'pear') or       (a1.name = 'cat' , a2.name = 'dog' , a3.name = 'mouse') or       (a1.name = 'python' , a2.name = 'perl' , a3.name = 'java') 

if want number selected inner query, following should work:

select a1.name name, @num numberselected (select b1.id id, @num := @num + 1       b1 join b2 join b3 ... cross join (select @num := 0) params       ...      )  ids 

the subquery should evaluated before outer query, value of @num should correct.