CODEWITHSUNDEEP
mysql
Deniss Muntjans
Deniss Muntjans

Reputation: 379

Using MySQL variable in WHERE clause

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'

Upvotes: 2

Views: 2454

Answers (2)

Álvaro González
Álvaro González

Reputation: 146630

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 SET statements you should never assign a value to a user variable and read the value within the same statement. [...] For other statements, such as SELECT, 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';

Upvotes: 5

Daniel W.
Daniel W.

Reputation: 32360

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'

Upvotes: 1

Related Questions