Link to home
Create AccountLog in
Avatar of ShanghaiD
ShanghaiDFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MySQL CASE WHEN with OR syntax and best practice

I'm still learning about MySQL so please excuse my ignorance.

I've read alternative syntax forms for CASE WHEN but it seems they do not all work the same way!  

I was initially using:  CASE variable WHEN condition THEN ....  but have also found CASE WHEN variable = condition THEN ... and they do not always produce the same results, as shown below:

-- correct for 3, fails for 'this':
SET @n=3;
SELECT @n, CASE @n
WHEN 'that' THEN 'one'
WHEN ('this'|3) THEN 'this or 3'
ELSE 'nothing' END;
SET @n='this';
SELECT @n, CASE @n
WHEN 'that' THEN 'one'
WHEN ('this'|3) THEN 'this or 3'
ELSE 'nothing' END;

-- fails for both
SET @n=3;
SELECT @n, CASE @n
WHEN 'that' THEN 'one'
WHEN 'this' OR 3 THEN 'this or 3'
ELSE 'nothing' END;
SET @n='this';
SELECT @n, CASE @n
WHEN 'that' THEN 'one'
WHEN 'this' OR 3 THEN 'this or 3'
ELSE 'nothing' END;

-- correct for 3, fails for 'this':
SET @n=3;
SELECT @n, CASE  
WHEN @n='that' THEN 'one'
WHEN @n=('this'|3) THEN 'this or 3'
ELSE 'nothing' END;
SET @n='this';
SELECT @n, CASE  
WHEN @n='that' THEN 'one'
WHEN @n=('this'|3) THEN 'this or 3'
ELSE 'nothing' END;

-- both work correctly:
SET @n=3;
SELECT @n, CASE  
WHEN @n='that' THEN 'one'
WHEN @n='this' OR @n=3 THEN 'this or 3'
ELSE 'nothing' END;
SET @n='this';
SELECT @n, CASE  
WHEN @n='that' THEN 'one'
WHEN @n='this' OR @n=3 THEN 'this or 3'
ELSE 'nothing' END;

It seems that only CASE WHEN variable = condition THEN ...   using OR but not | is guaranteed to give the right result each time and that both the CASE WHEN variable = condition THEN ... and the | operator are both unreliable?

Any insight or "best practice" guidance would be appreciated.
Avatar of jogos
jogos
Flag of Belgium image

The | bitwise compares your 'this' and 3 and uses that result to compare with your value
It does not mean 'this' OR 3

Try this to see what you are comparing with in your =
select ('this'|3)

Open in new window

SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account