Just like other programming languages, the structured query language has conditional statements that control the flow of data. The IF function returns a statement if condition is met and another type statement (or none at all) if not met. Let’s look at the SQL IF function using MySQL.
Preparation
This SQL Fiddle links to a sample schema that contains names, ages and gender – this is what we work with in this article. Create a table in your own IDE with this schema:
create table names ( id INT, name VARCHAR(50), age INT, gender VARCHAR(50) ); insert into names (id, name, age, gender) values (1, 'Bret Starkings', 55, 'M'); insert into names (id, name, age, gender) values (2, 'Bobbye Eyckel', 76, 'F'); insert into names (id, name, age, gender) values (3, 'Barbie Veschi', 50, 'F'); insert into names (id, name, age, gender) values (4, 'Electra Blazewicz', 47, 'F'); insert into names (id, name, age, gender) values (5, 'Estrella Borleace', 57, 'F'); insert into names (id, name, age, gender) values (6, 'Washington Pittwood', 75, 'M'); insert into names (id, name, age, gender) values (7, 'Phaedra Tertre', 27, 'F'); insert into names (id, name, age, gender) values (8, 'Nicolina Elie', 76, 'F'); insert into names (id, name, age, gender) values (9, 'Hugh Hughson', 86, 'M'); insert into names (id, name, age, gender) values (10, 'Dare Ravilious', 59, 'M'); insert into names (id, name, age, gender) values (11, 'Cad Levins', 13, 'M'); insert into names (id, name, age, gender) values (12, 'Sollie Kimbury', 33, 'M'); insert into names (id, name, age, gender) values (13, 'Elga Rearie', 61, 'F'); insert into names (id, name, age, gender) values (14, 'Cherey Terron', 54, 'F'); insert into names (id, name, age, gender) values (15, 'Abbie Kent', 23, 'F'); insert into names (id, name, age, gender) values (38, 'Nelia Picot', 2, 'F'); insert into names (id, name, age, gender) values (39, 'Benedetto Smithin', 33, 'M'); insert into names (id, name, age, gender) values (40, 'Rickie Maymand', 83, 'F'); insert into names (id, name, age, gender) values (41, 'Kristoforo Dashkov', 6, 'M'); insert into names (id, name, age, gender) values (42, 'Cherice Genty', 6, 'F'); insert into names (id, name, age, gender) values (43, 'Shirley Fake', 95, 'F'); insert into names (id, name, age, gender) values (44, 'Aeriel Plant', 36, 'F'); insert into names (id, name, age, gender) values (45, 'Halimeda Gook', 74, 'F'); insert into names (id, name, age, gender) values (46, 'Minor Harriot', 63, 'M'); insert into names (id, name, age, gender) values (47, 'Greggory Found', 61, 'M'); insert into names (id, name, age, gender) values (48, 'Vivien Braund', 94, 'F'); insert into names (id, name, age, gender) values (49, 'Gaylord Pochet', 96, 'M'); insert into names (id, name, age, gender) values (77, 'Nataline Sealand', 43, 'F')
IF()
IF(expression ,expr_true, expr_false);
The syntax for the IF function in a MySQL database uses the IF keyword and then takes in three parameters: the boolean expression the function is evaluating, the statement to return if the condition is true, and then the statement to return if the condition is false.
SELECT name, age, IF(age >= 18, "YES", "NO") AS OVER_18 FROM names;
Our query above selects the name and age of our entry then selects a conditional that we call OVER_18. Our SQL partial results are shown below (You can see full results in this SQL Fiddle):
| name | age | OVER_18 |
| Bret Starkings | 55 | YES |
| Bobbye Eyckel | 76 | YES |
| Barbie Veschi | 50 | YES |
| Electra Blazewicz | 47 | YES |
| Estrella Borleace | 57 | YES |
| Washington Pittwood | 75 | YES |
| Phaedra Tertre | 27 | YES |
| Nicolina Elie | 76 | YES |
| Hugh Hughson | 86 | YES |
| Dare Ravilious | 59 | YES |
| Cad Levins | 13 | NO |
IFNULL()
IFNULL() is a conditional statement that asks whether or not the first expression is NULL. If it is, it returns the second expression and not the first.
IFNULL(NULL, 2) ⇒ returns 2 IFNULL(0, 4) ⇒ returns 0 SELECT name, IFNULL(age, "NOT ENTERED") AS age FROM names;
This MySQL statement returns a column called age that will overwrite every NULL entry with the string “NOT ENTERED”.
NULLIF()
NULLIF() returns NULL when both expressions passed into the function are equal.
SELECT name, NULLIF(name, age) AS FROM names;
This is an extreme example because there would probably never be a case where the name and the age would be equal, but this is how the syntax would at least be written. Can you think of a case where this statement block would be beneficial?
Conclusion
In MySQL, there are a couple of different if functions that allow us to control the flow of our data. These concepts are translated to whichever database you are working with – check out the docs for more information about the syntax about your specific database.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.
