Menu
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').
725
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
A scalar subquery is a subquery that returns a single value. This is the simplest form of a subquery, and can be used in most places a literal or single column value is valid. The data type, length and character set and collation are all taken from the result returned by the subquery.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
