If else on WHERE clause

Question

I've this query:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE (
`email` LIKE  '%@domain.nl%'
OR  `email2` LIKE  '%@domain.nl%'
)

But I want to do something like this:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE IF(`email` > 0,
`email` LIKE  '%@domain.nl%'
,  `email2` LIKE  '%@domain.nl%'
)

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?

score 79 · Accepted Answer · answered Mar 01 '13 at 16:42

79

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  '%@domain.nl%'

answered Mar 01 '13 at 16:42

gen_Eric

223,194
41
299
337

On second tought I like your answer the best – botenvouwer Mar 01 '13 at 16:46
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? – TMH Jul 18 '14 at 14:21
@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()`? – gen_Eric Jul 21 '14 at 18:11
Not exactly, I only want it to check the start date if active is 1. – TMH Jul 21 '14 at 23:00
@TomHart: `WHERE active=1 AND startDate < UNIX_TIMESTAMP()`. I'm pretty sure MySQL will short circuit and stop evaluating if `active` isn't 1. – gen_Eric Jul 22 '14 at 13:22
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) – TMH Jul 22 '14 at 13:30
@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). – gen_Eric Jul 22 '14 at 13:37
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. – TMH Jul 22 '14 at 13:42
@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. – gen_Eric Jul 22 '14 at 13:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57761/discussion-between-tom-hart-and-rocket-hazmat). – TMH Jul 22 '14 at 13:56
Is that room available on chat.stachexchange? chat.stackoverflow is blocked at my work. – TMH Jul 22 '14 at 14:00
@TomHart: It should be, but I don't have time to join a chat right now. – gen_Eric Jul 22 '14 at 14:04
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 :). – TMH Jul 22 '14 at 14:08
This works on HeidiSql but SpringData nativeQuery. Do you know why? – menoktaokan Apr 05 '22 at 12:02
@menoktaokan What's the issue you're seeing? – gen_Eric Apr 05 '22 at 16:08
@RocketHazmat It was like Spring Data didn't recognize the syntax so I changed the path. – menoktaokan Apr 06 '22 at 12:57
I did it with a `BETWEEN` and it also worked, apparently the `IF` validates which column will be filtered. `WHERE IF (ca.schedule_date != '', DATE(ca.schedule_date), DATE(ca.date_created)) BETWEEN '2022-08-12' AND '2022-08-12'`; – Diego Lope Loyola Aug 18 '22 at 16:01

Gordon Linoff · Answer 2 · 2013-03-01T16:49:15.500

18

You want to use coalesce():

where coalesce(email, email2) like '%anja@fiskkoer.nl%'

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 '%anja@fiskkoer.nl%'

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 '%anja@fiskkoer.nl%'

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, 'abcd@de.com' would fail, because the string would convert as 0. As would '0abc@de.com'. But, '1abc@de.com' and '01abc@de.com' would succeed.

edited Mar 01 '13 at 16:49

answered Mar 01 '13 at 16:34

Gordon Linoff

1,242,037
58
646
786

@mtahmed No it won't - I've noted that in my answer. The question is not clear on what would be the correct check. – RB. Mar 01 '13 at 16:38
@mtahmed no, `COALESCE` will only work on `NULL`. But columns: email, email2 are nullable right? – John Woo Mar 01 '13 at 16:38
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 Mar 01 '13 at 16:42

score 3 · Answer 3 · answered Mar 01 '13 at 16:35

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  '%anja@fiskkoer.nl%'
OR (LENGTH(email) = 0 AND `email2` LIKE  '%anja@fiskkoer.nl%')

answered Mar 01 '13 at 16:35

RB.

36,301
12
91
131

Actually, does `email IS ''` work instead of `LENGTH(email) = 0`? – mmtauqir Mar 01 '13 at 16:40
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 %%. – botenvouwer Mar 01 '13 at 16:41
@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?) – RB. Mar 01 '13 at 16:42
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. – mmtauqir Mar 01 '13 at 18:28
1

@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. (http://dev.mysql.com/doc/refman/5.5/en/char.html) – Gordon Linoff Mar 01 '13 at 20:24
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). – mmtauqir Mar 22 '13 at 17:46

score 3 · Answer 4 · edited Mar 15 '20 at 08:09

3

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.

edited Mar 15 '20 at 08:09

Kate Orlova

3,225
5
11
35

answered Mar 15 '20 at 06:41

Thanura Jayasekara

82
1
5

score 2 · Answer 5 · edited Apr 07 '16 at 14:59

2

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

edited Apr 07 '16 at 14:59

mokk

916
17
35

answered Oct 28 '15 at 08:18

Ameen Maheen

2,719
1
28
28

If else on WHERE clause

5 Answers5

Linked