The MySQL IF ELSE statement with practical examples

Informatec Digital » Databases » The MySQL IF ELSE statement with practical examples

In this article, I'm going to show you how to use the MySQL IF ELSE statement with examples to improve your queries and get more accurate results. With these 18 practical examples, we'll learn how to use this powerful tool and get the most out of the database. We'll explore real-life cases where using IF ELSE makes a difference, from conditional data selection to performing complex operations. Are you ready? Let's go! Get ready to master this technique and take your SQL skills to the next level.

What is IF ELSE statement in MySQL?

Before we dive into the examples, it is important to understand what the IF ELSE statement is in MySQL. Basically, it is a conditional structure that allows us to execute different actions depending on whether a certain condition is met or not. That is, if the condition is true, a block of code will be executed, and if it is false, a different block of code will be executed.

MySQL IF ELSE Statement with Examples

Here we list a table with the 18 examples that we are going to see in this article:

Example	Description
1	Basic use of IF ELSE
2	IF ELSE with multiple conditions
3	Nested IF ELSE
4	IF ELSE with logical operators
5	IF ELSE with aggregation functions
6	IF ELSE with subqueries
7	IF ELSE with variables
8	IF ELSE with CASE
9	IF ELSE with IFNULL
10	IF ELSE with NULLIF
11	IF ELSE with EXISTS
12	IF ELSE with IN
13	IF ELSE with BETWEEN
14	IF ELSE with LIKE
15	IF ELSE with REGEXP
16	IF ELSE with DATE_FORMAT
17	IF ELSE with GROUP_CONCAT
18	IF ELSE with HAVING

1. Basic use of IF ELSE

Let’s look at a simple example to start with. Suppose we have a table called “products” with the following fields: id, name, price and stock. We want to get the name of the product and a message indicating whether it is in stock or not. We can do it like this:

SELECT nombre, IF(stock > 0, 'Hay stock', 'No hay stock') AS stock 
FROM productos;

In this case, the condition is stock > 0If it is met, the message 'In stock' will be displayed, and if it is not met, 'Out of stock' will be displayed.

2. IF ELSE with multiple conditions

We can also use IF ELSE with multiple conditions. For example, suppose we want to get the product name and a message indicating whether the price is greater than 100, less than 50, or between 50 and 100. We can do it like this:

SELECT nombre, 
  IF(precio > 100, 'Caro', 
    IF(precio < 50, 'Barato', 'Precio medio')
  ) AS precio
FROM productos;

In this case, we use nested IF ELSE statements to evaluate the different conditions.

3. Nested IF ELSE

As we saw in the previous example, we can nest multiple IF ELSE statements to evaluate different conditions. Let's look at another example. Suppose we want to get the product name and a message indicating whether the stock is greater than 100, less than 10, or between 10 and 100. Also, if the stock is less than 10, we want to indicate whether it is 0 or not. We can do it like this:

SELECT nombre,
  IF(stock > 100, 'Stock alto',
    IF(stock < 10, 
      IF(stock = 0, 'Sin stock', 'Stock bajo'),
      'Stock medio'
    )
  ) AS stock
FROM productos;

In this case, we use three levels of nesting to evaluate all the necessary conditions.

4. IF ELSE with logical operators

We can also combine IF ELSE with logical operators like AND and OR. For example, suppose we want to get the product name and a message indicating whether the price is greater than 100 and the stock is less than 10. We can do it like this:

SELECT nombre,
  IF(precio > 100 AND stock < 10, 'Urgente reponer', 'OK') AS estado
FROM productos;

In this case, the condition is precio > 100 AND stock < 10If both conditions are met, the message 'Urgent replenishment' will be displayed, and if they are not met, 'OK' will be displayed.

5. IF ELSE with aggregation functions

We can combine IF ELSE with aggregation functions like SUM, AVG, MAX, etc. For example, suppose we want to get the total sales and a message indicating whether we have exceeded the target of 10000 or not. We can do it like this:

SELECT 
  SUM(total) AS total_ventas,
  IF(SUM(total) > 10000, '¡Objetivo superado!', 'No se alcanzó el objetivo') AS mensaje
FROM ventas;

In this case, we calculate the total sales with SUM and then use IF ELSE to display a message based on whether the target has been exceeded or not.

6. IF ELSE with subqueries

We can also use IF ELSE with subqueriesFor example, let's say we want to get the name of the products and a message indicating whether their price is higher than the average price of all products. We can do it like this:

SELECT nombre,
  IF(precio > (SELECT AVG(precio) FROM productos), 
    'Precio superior a la media', 
    'Precio inferior o igual a la media'
  ) AS comparacion
FROM productos;

In this case, we use a subquery to calculate the average price of all products and then compare each individual price to that value using IF ELSE.

7. IF ELSE with variables

We can use IF ELSE statements in conjunction with variables to store and use values ​​in our queries. For example, suppose we want to get the total sales and a message indicating whether we have exceeded a target that we have stored in a variable. We can do this like this:

SET @objetivo = 10000;

SELECT 
  SUM(total) AS total_ventas,
  IF(SUM(total) > @objetivo, '¡Objetivo superado!', 'No se alcanzó el objetivo') AS mensaje 
FROM ventas;

In this case, we first define a variable @objetivo with the value 10000 and then we use it in the IF ELSE condition.

8. IF ELSE with CASE

Another way to use IF ELSE is to combine it with the CASE statementFor example, let's say we want to get the name of the product and a classification of its price into 'Cheap', 'Medium' or 'Expensive' based on certain ranges. We can do it like this:

SELECT nombre,
  CASE 
    WHEN precio < 50 THEN 'Barato'
    WHEN precio BETWEEN 50 AND 100 THEN 'Medio'
    ELSE 'Caro'
  END AS clasificacion
FROM productos;

In this case, we use CASE to evaluate different conditions and assign a value based on the result. It is an alternative way of doing the same thing as with nested IF ELSE statements.

9. IF ELSE with IFNULL

The IFNULL function allows us to handle null values ​​in our queries. We can combine it with IF ELSE to assign a default value when we encounter a null value. For example, suppose we want to get the product name and its price, but if the price is null, we want to display the message 'Query'. We can do it like this:

SELECT nombre, IFNULL(precio, 'Consultar') AS precio
FROM productos;

In this case, we use IFNULL to evaluate whether the price is null and if so, display the 'Query' message instead of the null value.

10. IF ELSE with NULLIF

The NULLIF function is similar to IFNULL, but instead of handling null values, it allows us to compare two values ​​and return NULL if they are equal. We can combine it with IF ELSE to assign an alternative value when a certain condition is met. For example, suppose we want to get the product name and its price, but if the price is 0, we want to display the message 'Free'. We can do it like this:

SELECT nombre, IF(NULLIF(precio, 0) IS NULL, 'Gratis', precio) AS precio
FROM productos;

In this case, we use NULLIF to compare the price to 0 and return NULL if they are equal. Then, we use IF ELSE to evaluate whether the result of NULLIF is NULL and if yes, display the message 'Free' instead of the price.

11. IF ELSE with EXISTS

The EXISTS clause allows us to check if any record exists that meets a certain condition in a subquery. We can combine it with IF ELSE to make decisions based on the result. For example, suppose we want to get the name of customers and a message indicating whether they have placed an order or not. We can do it like this:

SELECT nombre,
  IF(EXISTS(SELECT * FROM pedidos WHERE cliente_id = clientes.id),
    'Con pedidos',
    'Sin pedidos'
  ) AS estado
FROM clientes;

In this case, we use EXISTS with a subquery to check if any orders exist for each customer. Then, we use IF ELSE to display a message based on the result.

12. IF ELSE with IN

The IN operator allows us to check if a value is in a list of values. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the name of products and a message indicating whether they belong to the 'Electronics' or 'Home' category. We can do it like this:

SELECT nombre,
  IF(categoria IN ('Electrónica', 'Hogar'),
    'Producto destacado',
    'Producto regular'
  ) AS tipo
FROM productos;

In this case, we use IN to check if the product category is listed under 'Electronics' or 'Home'. Then, we use IF ELSE to display a message based on the result.

13. IF ELSE with BETWEEN

The BETWEEN operator allows us to check if a value is within a range of values. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the name of the products and a message indicating whether their price is between 50 and 100. We can do it like this:

SELECT nombre,
  IF(precio BETWEEN 50 AND 100,
    'Precio en rango',
    'Precio fuera de rango'
  ) AS rango
FROM productos;

In this case, we use BETWEEN to check if the price of the product is between 50 and 100. Then, we use IF ELSE to display a message based on the result.

14. IF ELSE with LIKE

The LIKE operator allows us to search for patterns in text strings. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the name of products and a message indicating whether their name contains the word 'iPhone'. We can do it like this:

SELECT nombre,
  IF(nombre LIKE '%iPhone%',
    'Producto de Apple',
    'Otro producto'
  ) AS tipo
FROM productos;

In this case, we use LIKE to check if the product name contains the word 'iPhone'. Then, we use IF ELSE to display a message based on the result.

15. IF ELSE with REGEXP

The REGEXP operator allows us to search for more complex patterns in text strings using regular expressions. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the customers' name and a message indicating whether their email is valid or not. We can do it like this:

SELECT nombre,
  IF(email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z]{2,}$',
    'Email válido',
    'Email inválido'
  ) AS validacion
FROM clientes;

In this case, we use REGEXP with a regular expression to check if the customer's email meets a valid format. Then, we use IF ELSE to display a message based on the result.

16. IF ELSE with DATE_FORMAT

The DATE_FORMAT function allows us to format dates in our queries. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the name of the orders and a message indicating whether they were made on a weekend or not. We can do it like this:

SELECT id,
  IF(DATE_FORMAT(fecha, '%w') IN (0, 6),
    'Fin de semana',
    'Día de semana'
  ) AS dia
FROM pedidos;

In this case, we use DATE_FORMAT to get the day of the week of the order date (0 for Sunday, 6 for Saturday). Then, we use IF ELSE with IN to check if the day is 0 or 6 and display a message based on the result.

17. IF ELSE with GROUP_CONCAT

The GROUP_CONCAT function allows us to concatenate values ​​from multiple rows into a single string. We can combine it with IF ELSE to make decisions based on the result. For example, suppose we want to get the name of customers and a message indicating whether they purchased 'iPhone' or 'Samsung'. We can do so like this:

SELECT c.nombre,
  IF(GROUP_CONCAT(p.nombre) LIKE '%iPhone%', 
    'Compró iPhone',
    IF(GROUP_CONCAT(p.nombre) LIKE '%Samsung%',
      'Compró Samsung',
      'No compró iPhone ni Samsung'
    )
  ) AS mensaje
FROM clientes c
JOIN pedidos pe ON c.id = pe.cliente_id
JOIN productos p ON pe.producto_id = p.id
GROUP BY c.id;

In this case, we use GROUP_CONCAT to concatenate the names of the products purchased by each customer. Then, we use IF ELSE with LIKE to check if the resulting string contains 'iPhone' or 'Samsung' and display a message based on the result.

18. IF ELSE with HAVING

La HAVING clause allows us to filter the results of a query with GROUP BY based on certain conditions. We can combine it with IF ELSE to make decisions based on the result. For example, let's say we want to get the number of orders per customer and a message indicating whether they are "VIP Customer" (more than 10 orders) or "Regular Customer". We can do it like this:

SELECT c.nombre, COUNT(p.id) AS total_pedidos,
  IF(COUNT(p.id) > 10,
    'Cliente VIP',
    'Cliente regular'
  ) AS tipo_cliente
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.id
HAVING COUNT(p.id) > 5;

In this case, we use GROUP BY to group orders by customer and count the number of orders for each customer using COUNT. Then, we use HAVING to filter out only customers with more than 5 orders. Finally, we use IF ELSE to display a message based on whether the total orders are greater than 10 or not.

FAQ on IF ELSE MySQL with examples

1. What is IF ELSE statement in MySQL?

The IF ELSE statement in MySQL is a conditional structure which allows us to execute different actions depending on whether a certain condition is met or not.

2. How to use IF ELSE statement in MySQL?

The basic syntax of the IF ELSE statement in MySQL is as follows:

IF(condición, valor_si_verdadero, valor_si_falso)

3. Can multiple IF ELSE statements be nested in MySQL?

Yes, you can nest multiple IF ELSE statements to evaluate different conditions and make decisions based on the results.

4. Can IF ELSE be combined with other MySQL functions and clauses?

Yes, IF ELSE can be combined with many other MySQL functions and clauses, such as aggregate functions, subqueries, CASE, IFNULL, NULLIF, EXISTS, IN, BETWEEN, LIKE, REGEXP, DATE_FORMAT, GROUP_CONCAT, HAVING, and more.

5. What is the difference between IF ELSE and CASE in MySQL?

Both IF ELSE and CASE allow us to execute different actions based on certain conditions. The main difference is that IF ELSE is used to evaluate a single condition and execute an action if it is met or not, whereas CASE is used to evaluate multiple conditions and execute different actions based on the result.

6. Are there any limitations in using IF ELSE in MySQL?

There are no significant limitations to using IF ELSE in MySQL, beyond the limitations of the logic and syntax of the conditions being evaluated. However, it is important to note that overuse or misuse of IF ELSE can affect query performance and make code harder to read and maintain.

Conclusion of IF ELSE MySQL with examples

In this article, we have learned what the IF ELSE statement is in MySQL and how it is used to execute different actions based on certain conditions. We have seen 18 practical examples of use of IF ELSE, combining it with other MySQL functions and clauses such as aggregate functions, subqueries, CASE, IFNULL, NULLIF, EXISTS, IN, BETWEEN, LIKE, REGEXP, DATE_FORMAT, GROUP_CONCAT, and HAVING.

I hope these examples have been useful and that they help you better understand how to use IF ELSE in your consultations from MySQL. And don't forget to share this article with your friends and colleagues if you found it interesting!