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


Surely do it like this



SET @condition := (SELECT * FROM t2 WHERE t2.foo=='bar');
SELECT * FROM t1
LEFT JOIN (@condition) ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN (@condition) ON t1.id = t2.id

      

+2


source







All Articles