CODEWITHSUNDEEP
mysql
Sideshow Bob
Sideshow Bob

Reputation: 4716

Store mysql subquery in a variable

Is is possible to store a mysql subquery somehow, if it will be used again as a subquery? Presumably this would produce cleaner code as well as save parsing overheads.

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 (SELECT * FROM t2 WHERE t2.foo=='bar').

Upvotes: 11

Views: 21085

Answers (3)

LadyBug
LadyBug

Reputation: 61

MySQL has with function since version 8.0. Making @ypercube answer correct.

so now you can do for instance:

WITH subquery AS
  (SELECT * FROM t2 WHERE t2.foo = 'bar')
SELECT * FROM t1
LEFT JOIN subquery  ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN subquery ON t1.id = t2.id

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

No, you can't. If MySQL had CTE (Common Table Expressions), you could 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 (which alas, it hasn't either!), you could use this:

SELECT * FROM t1
  FULL JOIN (SELECT * FROM t2 WHERE t2.foo = 'bar') tmp
    ON t1.id = tmp.id

Upvotes: 8

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Of course 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

Upvotes: 6

Related Questions