How do I execute a Subquery once in a Query like this
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.
If you want a single result set with the first row containing a list of all
table_a, and subsequent rows listing all
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.
UNION checks for and eliminates duplicate rows;
UNION ALL just combines all rows without checking. Since we don’t need to worry about duplicates….)
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;