SQL case statement not executing else part Problem statement:

Create a function to search a student with roll_no; if exists show "Student Name and Course" else show "Roll No not exist".

CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
RETURN 
(SELECT CASE 
        WHEN `roll_no`= roll_num  THEN CONCAT (`student`.`student_name`,`student`.`course_id`)
        ELSE 'Roll No not exist '
        END
FROM `student`WHERE `student`.`roll_no` = roll_num);

SELECT search(31219005) ; //since this is an existing roll num everything went correct.
SELECT search(21219005) ; //this roll is not an existing one in the DB, but it simply shows NULL , instead of existing my else part.
This returns the wanted result, but i don't think that you wnat only the course id.

But this should help further

DELIMITER $$
CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
DETERMINISTIC
BEGIN
    IF (EXISTS(SELECT 1 FROM `student` WHERE `student`.`roll_no` = roll_num)) THEN
        RETURN (SELECT CONCAT (`student`.`student_name`,`student`.`course_id`) FROM `student`WHERE `student`.`roll_no` = roll_num);
    ELSE
        RETURN 'Roll No not exist ';
    END IF;
END$$
DELIMITER ;