MySQL IF() Function: Conditional Values in Queries

IF(condition, value_if_true, value_if_false) returns one value when a condition is true and another when it is false. For more complex branching, see CASE.

SELECT id, name, mark, IF(mark >= 60, 'PASS', 'FAIL') AS result
FROM student
LIMIT 10;

Output:

id	name	mark	Result
1	John Deo	75	PASS
2	Max Ruin	85	PASS
3	Arnold	55	FAIL
4	Krish Star	60	PASS
5	John Mike	60	PASS
6	Alex John	55	FAIL
7	My John Rob	78	PASS
8	Asruid	85	PASS
9	Tes Qry	78	PASS
10	Big John	55	FAIL

Download the student table SQL dump

Syntax

IF(condition, value_if_true, value_if_false)

SQLite IIF() equivalent

Pass or fail from multiple marks

SELECT id, name, social + math + science AS total,
       IF(social + math + science > 200, 'Pass', 'Fail') AS result
FROM student3;

Output:

id	name	total	Result
2	Max Ruin	226	Pass
3	Arnold	170	Fail
4	Krish Star	180	Fail
5	John Mike	230	Pass
6	Alex John	225	Pass
7	My John Rob	208	Pass
8	Asruid	255	Pass
9	Tes Qry	208	Pass
10	Big John	150	Fail

Download the student3 table SQL dump

The old version of this example wrapped the row expression in SUM() and grouped by a unique id. That aggregation is unnecessary when the goal is to calculate one row's total.

SELECT id, name, social + math + science AS total
FROM student3;

The earlier examples combine naturally with LIMIT and BETWEEN. Conditional totals also connect to SUM() and COUNT().

Conditional aggregation with IF()

SUM(IF(...)) can count rows that satisfy each condition.

SELECT
  SUM(IF(class = 'Three', 1, 0)) AS three,
  SUM(IF(class = 'Four', 1, 0)) AS four,
  SUM(IF(class = 'Five', 1, 0)) AS five,
  SUM(IF(class = 'Six', 1, 0)) AS six,
  SUM(IF(class = 'Seven', 1, 0)) AS seven
FROM student;

Output:

THREE	FOUR	FIVE	SIX	SEVEN	EIGHT	NINIE
3	9	3	7	10	1	2

COUNT(IF(condition,1,NULL)) is another common pattern because COUNT(expr) ignores NULL.

SELECT
  COUNT(IF(class = 'Three', 1, NULL)) AS three,
  COUNT(IF(class = 'Four', 1, NULL)) AS four,
  COUNT(IF(class = 'Five', 1, NULL)) AS five
FROM student;

Output:

THREE	FOUR	FIVE	SIX	SEVEN	EIGHT	NINIE
3	9	3	7	10	1	2

Grade totals by class

SELECT class,
  SUM(IF(mark < 50, 1, 0)) AS grade_C,
  SUM(IF(mark BETWEEN 50 AND 69, 1, 0)) AS grade_B,
  SUM(IF(mark >= 70, 1, 0)) AS grade_A
FROM student
GROUP BY class;

Output:

class	grade_C	grade_B	grade_A
Eight	0	0	1
Five	0	0	3
Four	0	5	4
Nine	1	1	0
Seven	0	3	7
Six	0	1	6
Three	0	1	2

MySQL IF function return data based on True or False condition matching with GROUP BY and BETWEEN SQL

CASE Expression IFNULL() & COALESCE() GROUP BY

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Rajnit Baldaniya	10-02-2018
SELECT id,Mark, CASE WHEN Mark > 35 THEN 'PASS' ELSE 'FAIL' END AS Result FROM marks