If else on WHERE clause

Question

I've this query:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE (
`email` LIKE  '[e-mail]%'
OR  `email2` LIKE  '[e-mail]%'
)

But I want to do something like this:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE IF(`email` > 0,
`email` LIKE  '[e-mail]%'
,  `email2` LIKE  '[e-mail]%'
)

How to check if email exist? I want to use email and if this field is empty I want to use email2. How do I accomplish this?

Solution 1

IF is used to select the field, then the LIKE clause is placed after it:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE IF(`email` != '', `email`, `email2`) LIKE  '[e-mail]%'

Solution 2

You want to use coalesce():

where coalesce(email, email2) like '%[email protected]%'

If you want to handle empty strings ('') versus NULL, a case works:

where (case when email is NULL or email = '' then email2 else email end) like '%[email protected]%'

And, if you are worried about the string really being just spaces:

where (case when email is NULL or ltrim(email) = '' then email2 else email end) like '%[email protected]%'

As an aside, the sample if statement is really saying "If email starts with a number larger than 0". This is because the comparison is to 0, a number. MySQL implicitly tries to convert the string to a number. So, '[email protected]' would fail, because the string would convert as 0. As would '[email protected]'. But, '[email protected]' and '[email protected]' would succeed.

Solution 3

Note the following is functionally different to Gordon Linoff's answer. His answer assumes that you want to use email2 if email is NULL. Mine assumes you want to use email2 if email is an empty-string. The correct answer will depend on your database (or you could perform a NULL check and an empty-string check - it all depends on what is appropriate for your database design).

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE `email` LIKE  '%[email protected]%'
OR (LENGTH(email) = 0 AND `email2` LIKE  '%[email protected]%')

Solution 4

Here is a sample query for a table having a foreign key relationship to the same table with a query parameter.

SET @x = -1;
SELECT id, categoryName 
FROM Catergory WHERE IF(@x > 0,category_ParentId = @x,category_ParentId IS NOT NULL);

@x can be changed.

Solution 5

try this ,hope it helps

select user_display_image as user_image,
user_display_name as user_name,
invitee_phone,
(
 CASE 
    WHEN invitee_status=1 THEN "attending" 
    WHEN invitee_status=2 THEN "unsure" 
    WHEN invitee_status=3 THEN "declined" 
    WHEN invitee_status=0 THEN "notreviwed" END
) AS  invitee_status
 FROM your_tbl

View more solutions

Comments

[Maurício]

@mtahmed No it won't - I've noted that in my answer. The question is not clear on what would be the correct check.

[John Woo]

@mtahmed no, COALESCE will only work on NULL. But columns: email, email2 are nullable right?

[mmtauqir]

Actually, does email IS '' work instead of LENGTH(email) = 0?

[botenvouwer]

this works for me thank you for you answer. Note that I look for the domain and not the email itself. That is why I use LIKE %%.

[Maurício]

@mtahmed Yes, that does work. I was just copying the question as closely as possible, so my answer would be more understandable to the author (although it would be email = '' surely?)

[botenvouwer]

The field or the table cell if you like is just '' and not NULL so there you go. ps, the database itself is not made by me!

[botenvouwer]

On second tought I like your answer the best

[mmtauqir]

The reason I am asking was because when checking for empty string, it's almost always better to do string compare (email = '') instead of checking length (LENGTH(email) = 0) because it's faster to do string compare.

[Gordon Linoff]

@mtahmed . . . Do you have a reference on why the string compare is faster than the length? Given that the length is stored prior to any data in the string, I would expect a length comparison to possibly be faster. (dev.mysql.com/doc/refman/5.5/en/char.html)

[mmtauqir]

Oh I meant it as a general comment: I use string compare whenever I can. Also, it's a one byte compare for string compare while it's 4 or 8 byte compare for length compare (comparing char vs integer).

[TMH]

Can I use IF to filter out more fields? I want to do if (active = 1) AND startDate < UNIX_TIMESTAMP(). Is something like that possible?

[gen_Eric]

@TomHart: IF here is just used to select one field vs the other. It makes no sense in your example. Do you mean: WHERE active=1 AND startDate < UNIX_TIMESTAMP()?

[TMH]

Not exactly, I only want it to check the start date if active is 1.

[gen_Eric]

@TomHart: WHERE active=1 AND startDate < UNIX_TIMESTAMP(). I'm pretty sure MySQL will short circuit and stop evaluating if active isn't 1.

[TMH]

So if I have the startDate criteria at the very end of the WHERE, it should work as intended? (I don't have access to check this at the moment)

[gen_Eric]

@TomHart: I think so. I'm not 100% sure that MySQL does short circuit evaluation. You can also try WHERE IF(active = 1, startDate < UNIX_TIMESTAMP(), 0) (or whatever you want to happen if active is not 1).

[TMH]

I'll look at the IF syntax now, basically if active is 0 (it's only either 1 or 0) it ignores the start date.

[gen_Eric]

@TomHart: Are there more criteria after that? You can also try WHERE CASE WHEN active=1 THEN startDate < UNIX_TIMESTAMP() END. That might be better here.

[TMH]

Let us continue this discussion in chat.

[TMH]

Is that room available on chat.stachexchange? chat.stackoverflow is blocked at my work.

[gen_Eric]

@TomHart: It should be, but I don't have time to join a chat right now.

[TMH]

Okay not a problem, I have a working solution at the moment, but I'll look as using CASE instead as it seems more elegant. Thanks for your help :).

[menoktaokan]

This works on HeidiSql but SpringData nativeQuery. Do you know why?

[gen_Eric]

@menoktaokan What's the issue you're seeing?

[menoktaokan]

@RocketHazmat It was like Spring Data didn't recognize the syntax so I changed the path.