UNION SELECT and initializing user-defined variable

Question

I have a relatively long union select:

select A, B from table1 where C='xxx'
union select A, B from table2 where C='xxx'
union select A, B from table3 where C='xxx'
...

I would like to define xxx as a user-defined variable so I don't have to repeat the condition many time. Unfortunately my MySQL client (visualization) does not support multiple statements so I cannot use SET in the beginning. Is there a way to include the user-defined variable into such statement without changing the SQL output?

Put it in a table? `... where C = (select value from sqlfragments where id = 1)` — Alex K., Mar 29 '21 at 15:03

score 0 · Answer 1 · answered Mar 29 '21 at 15:12

You can use @varname:='value' pretty much anywhere in a query. In this case you can:

select A, B 
from table1 
JOIN (SELECT @x:='xxx') x 
where C=@x
union select A, B from table2 where C=@x
union select A, B from table3 where C=@x
...

Or you can add one more union:

SELECT null,null FROM (SELECT @x:='xxx') x WHERE 0
union select A, B from table1 where C=@x
union select A, B from table2 where C=@x
union select A, B from table3 where C=@x
...

You can put @x:='xxx' wherever is convenient, just be mindful about the order of execution.

UNION SELECT and initializing user-defined variable

1 Answers1