2

How do I execute a Subquery once in a Query like this

http://sqlfiddle.com/#!9/8b149d/1/0

select 
id, 
(
  select group_concat(user) 
  from table_b
) as subquery 
from table_a

to maintain performance of the Query, Or it is better If I run two Queries separatly?

I want a result as if I used

select group_concat(user) from talbe_b;
select id from table_a;

but in a single query.

  • What would you like the output of your query to be? – gareth Mar 7 '18 at 16:43
  • @gareth I want to get all users from table_b to be used in a different place once, Something like if first row has the group_concat and all rows after it is about the id – Toleo Mar 7 '18 at 16:46
  • 2
    Why not run 2 separate queries? It can be done in a single query but the value from group_concat will be returned a thousand (or a million) times, as many as the rows in table_a. – ypercubeᵀᴹ Mar 7 '18 at 16:52
  • @ypercubeᵀᴹ Which means the only way is using two different queries. – Toleo Mar 7 '18 at 16:58
  • 1
    Wait, is your question really "how to return the value only once?" If yes, it's not clear from the question at all. – ypercubeᵀᴹ Mar 7 '18 at 17:02
4

If you want a single result set with the first row containing a list of all users from table_a, and subsequent rows listing all ids from table_b (one per row), you could UNION the two queries. However, you would have to cast the results to a common data type (presumably something like varchar), and you would technically need to include a sort order field to guarantee that the users are in the first row.

SELECT values
FROM (select group_concat(user) as values, 1 as sort_order from table_b
      UNION ALL
      select CAST(id as varchar) as values, 2 as sort_order from table_a
     ) sq
ORDER BY sort_order
;

This will almost certainly be at least slightly slower than running the two queries separately. Not sure if that will matter, in your circumstance.

(Note: UNION checks for and eliminates duplicate rows; UNION ALL just combines all rows without checking. Since we don't need to worry about duplicates....)

3

Starting in MariaDB 10.2 and MySQL 8.0 you can do Common Table Expressions, so:

WITH subquery AS (SELECT group_concat(user) AS user FROM table_b)
  SELECT table_a.id, subquery.user FROM table_a, subquery; 
  • It still has the same effect of the original query, giving each row the concat value. – Toleo Mar 7 '18 at 16:57
  • 2
    @Matheo the subquery/CTE is executed/evaluated once in this case. The value is returned multiple times, as your original query. – ypercubeᵀᴹ Mar 7 '18 at 17:02
  • 3
    You could also use a derived table: SELECT table_a.id, subquery.user FROM table_a CROSS JOIN (SELECT group_concat(user) AS user FROM table_b) subquery; Same results, and the subquery evaluated only once, as the CTE version in the answer. – ypercubeᵀᴹ Mar 7 '18 at 17:04
  • @ypercubeᵀᴹ well, it seems as u said, two queries is better, also having multiple rows isn't better than a new query with a single one as i suppose. – Toleo Mar 7 '18 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.