Store mysql subquery in variable
Is it possible to somehow save the mysql subquery if it will be used again as a subquery? Presumably this would result in cleaner code as well as savings in parsing overhead.
For example, in the following outer join
SELECT * FROM t1
LEFT JOIN (SELECT * FROM t2 WHERE t2.foo=='bar') ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN (SELECT * FROM t2 WHERE t2.foo=='bar') ON t1.id = t2.id
It would be nice not to repeat it (SELECT * FROM t2 WHERE t2.foo=='bar')
.
+3
source to share
2 answers
No, you cannot. If MySQL has CTE (Common Table Expressions) you can use this:
WITH tmp AS
(SELECT * FROM t2 WHERE t2.foo = 'bar')
SELECT * FROM t1
LEFT JOIN tmp ON t1.id = tmp.id
UNION
SELECT * FROM t1
RIGHT JOIN tmp ON t1.id = tmp.id
If MySQL had FULL JOIN
(alas, neither does!), You can use this:
SELECT * FROM t1
FULL JOIN (SELECT * FROM t2 WHERE t2.foo = 'bar') tmp
ON t1.id = tmp.id
+3
source to share