# How to use 'IF' in 'SELECT' SQL statements?

By Bobby Iliev, 2022-03-07. https://bobbyiliev.com/post/how-to-use-if-in-select-sql-statements/

# Introduction

In some cases, you might want to choose the output value based on column values directly in your SQL statement.

In this article, we will explain how to use the `CASE` expression in `SELECT` SQL statements. We will also explain how to use an alternative approach with an `IF` clause for MySQL.

# Prerequisites

For the sake of simplicity, we will use a sample table called `users` with the following columns:

```sql
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);
```

And let's insert some data into it:

```sql
INSERT INTO users (id, name, status, email) VALUES (1, 'John', 'pending', 'john@demo.com');
INSERT INTO users (id, name, status, email) VALUES (2, 'Jane', 'active', 'jane@demo.com');
```

# `CASE` in `SELECT` SQL statements

Let's start with the `CASE` expression. The `CASE` expression is used to evaluate a condition and return a value based on the result:

```sql
SELECT id, name,
    CASE users.status
        WHEN 'pending' THEN false
        WHEN 'verified' THEN true
    END AS status
FROM users;
```

Output:

```
 id | name  | status
----+-------+--------
  1 | test  | f
  2 | test2 | t
```

A quick rundown of the above query:

- `SELECT id, name,`: Selects the columns `id` and `name` from the table `users`.
- `CASE users.status`: Starts the `CASE` expression with the column `status` from the table `users`.
- `WHEN 'pending' THEN false`: When the value of the column `status` is `pending`, return `false`.
- `WHEN 'verified' THEN true`: When the value of the column `status` is `verified`, return `true`.
- `END`: End the `CASE` expression.

In most cases, you will want to use the `CASE` expression instead of the `IF` clause. As the `CASE` expression is ANSI standard, making it portable to other databases without the need for alteration.

# `IF` in `SELECT` SQL statements

If you are using **MySQL**, you can use the `IF` clause instead of the `CASE` expression.

Here is the same query as above, but using the `IF` clause:

```sql
SELECT id, name,
       IF(status = 'pending', false, true) as status
FROM users;
```

Output:

```sql
+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | John |      0 |
|  2 | Jane |   NULL |
+----+------+--------+
```

You need to keep in mind that in Postgres the `IF` statement is part of the default procedural language PL/pgSQL:

- [PL/pgSQL Conditionals](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS)

And also `IF` is typically used for logic flow, where `CASE` is used to return data values based on the result of a condition.

# Conclusion

This is all! Now you should have a better idea of how to choose the output value based on column values in a table using `IF` or a `CASE` expression.

In case you want to learn more about SQL in general, I would recommend the following free eBook:

 - [Introduction to SQL open-source eBook](/post/opensource-introduction-to-sql-ebook/)

In case you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out [Materialize](https://materialize.com/)!

![Materialize - a streaming database](https://imgur.com/52d9a6h.png)

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.