Using an IF Statement in a MySQL SELECT query

Question

I am trying to use an IF statement in a MySQL select query.

I am getting an error after the AND statement where the first IF.

SELECT J.JOB_ID,E.COMPANY_NAME,J.JOB_DESC,JT.JOBTYPE_NAME,J.COMPENSATION,ST.STATE_NAME,MC.METRO_CITY_NAME,I.INDUSTRY_NAME,
 J.JOB_CONTACT_PERSON,J.DT_INSRT,J.JOB_TITLE,J.JOB_EXP_DATE,J.SKILLS
 FROM JOBS J 
 JOIN EMPLOYER E ON J.COMPANY_ID=E.COMPANY_ID 
 JOIN LOOKUP_JOBTYPE JT ON J.JOB_TYPE=JT.JOBTYPE_ID
 JOIN LOOKUP_STATE ST ON J.STATE_ID=ST.STATE_ID
 JOIN JOBS_LOCATION JL ON J.JOB_ID=JL.JOB_ID
 JOIN LOOKUP_METRO_CITY MC ON JL.METRO_CITY_ID=MC.METRO_CITY_ID
 JOIN LOOKUP_INDUSTRY I ON J.INDUSTRY_ID=I.INDUSTRY_ID 
 JOIN JOBS_QUALIFICATION JQ ON J.JOB_ID=JQ.JOB_ID 
 JOIN LOOKUP_DEGREE_QUALIFICATION LDQ ON LDQ.QUALIFICATION_ID = JQ.QUALIFICATION_ID
 WHERE  J.ACTIVE='Y' AND J.DT_INSRT > COALESCE(pEmailSntDt,DATE_SUB(SYSDATE(),INTERVAL 4 DAY))  
AND
IF(JQ.COURSE_ID=0) 
THEN
IF(JQ.DEGREE_ID=0)
THEN J.SKILLS LIKE CONCAT('%', pSkills,'%')
ELSE
JQ.DEGREE_ID=pDegreeId OR J.SKILLS LIKE CONCAT('%', pSkills,'%')
END IF
ELSE
JQ.COURSE_ID=pCourseId OR IF(JQ.DEGREE_ID=0)
                      THEN
                      J.SKILLS LIKE CONCAT('%', pSkills,'%')
                      ELSE
                      JQ.DEGREE_ID=pDegreeId OR J.SKILLS LIKE CONCAT('%', pSkills,'%')
                      END IF
END IF                           
GROUP BY J.JOB_ID ORDER BY J.DT_INSRT DESC;

Why doesn't this work and what is the proper way to do an IF statement in a MySQL query?

I don't think you can use that instructions in a query, in a `WHERE` clause. Maybe you can rewrite the query using **`IF()`** function instead? http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if — lorenzo-s, Dec 09 '11 at 11:09

score 64 · Accepted Answer · answered Dec 09 '11 at 12:04

64

The IF/THEN/ELSE construct you are using is only valid in stored procedures and functions. Your query will need to be restructured because you can't use the IF() function to control the flow of the WHERE clause like this.

The IF() function that can be used in queries is primarily meant to be used in the SELECT portion of the query for selecting different data based on certain conditions, not so much to be used in the WHERE portion of the query:

SELECT IF(JQ.COURSE_ID=0, 'Some Result If True', 'Some Result If False'), OTHER_COLUMNS
FROM ...
WHERE ...

answered Dec 09 '11 at 12:04

davidethell

11,708
6
43
63

1

iam not selecting columns using IF.I want to frame query based on conditions.for that iam using IF Else. – Santosh Dec 09 '11 at 12:50
I understand that, I'm just explaining that's not what the IF function is meant to do and that you can't use the IF/THEN/ELSE logic in queries. If you want to do this, you can create a stored function with your logic that returns a value that you can use in your WHERE clause. – davidethell Dec 09 '11 at 12:57
can we use CASE statements instead of IF – Santosh Dec 09 '11 at 13:04
1

Same thing with CASE. It is really meant to be used as a dynamic way to select a return value rather than a way to put conditional elements in the WHERE clause. – davidethell Dec 10 '11 at 11:58
what if there are more if conditions? for one column output? Like an If-else statement with the same format of the solution above – Ann Nacional Jan 11 '17 at 01:51

Eric Leschinski · Answer 2 · 2015-01-29T03:16:07.750

How to use an IF statement in the MySQL "select list":

select if (1>2, 2, 3);                         //returns 3
select if(1<2,'yes','no');                     //returns yes
SELECT IF(STRCMP('test','test1'),'no','yes');  //returns no

How to use an IF statement in the MySQL where clause search condition list:

create table penguins (id int primary key auto_increment, name varchar(100))
insert into penguins (name) values ('rico')
insert into penguins (name) values ('kowalski')
insert into penguins (name) values ('skipper')

select * from penguins where 3 = id
-->3    skipper

select * from penguins where (if (true, 2, 3)) = id
-->2    kowalski

How to use an IF statement in the MySQL "having clause search conditions":

select * from penguins 
where 1=1
having (if (true, 2, 3)) = id
-->1    rico

Use an IF statement with a column used in the select list to make a decision:

select (if (id = 2, -1, 1)) item
from penguins
where 1=1
--> 1
--> -1
--> 1

If statements embedded in SQL queries is a bad "code smell". Bad code has high "WTF's per minute" during code review. This is one of those things. If I see this in production with your name on it, I'm going to automatically not like you.

score 19 · Answer 3 · edited Jun 11 '17 at 19:47

19

try this code worked for me

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_table

edited Jun 11 '17 at 19:47

Alexis Wilke

19,179
10
84
156

answered Oct 28 '15 at 08:15

Ameen Maheen

2,719
1
28
28

Using an IF Statement in a MySQL SELECT query

3 Answers3

Linked