How to properly use MySQL variable in WHERE clause? Instead of column1, column2, column3 I've got massive queries that is why I think it's better to use variables for that. Don't know what I'm doing wrong!
SELECT
@TEST:= CASE
WHEN column1=1 THEN 'ONE'
WHEN column1=2 THEN 'TWO'
WHEN column1=2 THEN 'THREE'
ELSE 'Unknown'
END AS test
FROM
table1
WHERE
@TEST= 'ONE'
If I guessed correctly, you want to avoid repeating yourself:
SELECT CASE
WHEN column1=1 THEN 'ONE'
WHEN column1=2 THEN 'TWO'
WHEN column1=2 THEN 'THREE'
ELSE 'Unknown'
END AS test
FROM table1
WHERE CASE
WHEN column1=1 THEN 'ONE'
WHEN column1=2 THEN 'TWO'
WHEN column1=2 THEN 'THREE'
ELSE 'Unknown'
END = 'ONE';
... but the obvious approach triggers an error:
SELECT CASE
WHEN column1=1 THEN 'ONE'
WHEN column1=2 THEN 'TWO'
WHEN column1=2 THEN 'THREE'
ELSE 'Unknown'
END AS test
FROM table1
WHERE test = 'ONE';
ERROR 1054 (42S22): Unknown column 'test' in 'where clause'
... so you've figured out you could use variables as in any other programming language.
For good or bad, SQL is not a programming language (it's a query language) and variables don't behave this way. Generally speaking, you cannot use variables to replace code fragments. And using them to store values (as in your example) is pretty risky, as the manual warns:
other than in
SETstatements you should never assign a value to a user variable and read the value within the same statement. [...] For other statements, such asSELECT, you might get the results you expect, but this is not guaranteed.
In this situation, repeating the entire expression is probably the only sensible way to go. If the actual code is huge and difficult to maintain, you can always generate your SQL dynamically in your client language (PHP, Java, whatever...). MySQL itself has some dynamic SQL features but they aren't as handy.
In specific circumstances, you could also play with subqueries, but be warned that performance may sometimes suffer:
SELECT *
FROM (
SELECT CASE
WHEN column1=1 THEN 'ONE'
WHEN column1=2 THEN 'TWO'
WHEN column1=2 THEN 'THREE'
ELSE 'Unknown'
END AS test
FROM table1
) t
WHERE test = 'ONE';
I think WHERE is evaluated applied before SELECT which means your query isn't possible at all that way (not 100% sure tho).
You can do it this way which is essentially the same:
SELECT
column1 AS TEST
FROM
table1
WHERE
(column1 = 1 AND ONE = 'ONE')
OR (column1 = 2 AND TWO = 'ONE')
OR (column1 = 3 AND THREE = 'ONE')
OR Unknown = 'ONE'
column1, column2, column3 I've got massive queries that is why I think it's better to use variables for that. But I don't know what I'm doing wrong!