Conditional (CASE)

Source - commandprompt.com.

Conditional SELECT allows you to define which values you get for certain cases in data values. In general, you can use something like this inside a SELECT block:

CASE
WHEN <condition 1> THEN <result 1>
WHEN <condition 2> THEN <result 2>
…
WHEN <condition n> THEN <result n>
ELSE <else result>
END

Note ELSE block is optional.

--postgreSQL
CREATE TABLE tab (
    var1 INT,
    var2 INT,
    var3 TEXT
);

INSERT INTO tab (var1, var2, var3) VALUES
(1, 2, 'foo'),
(4, 5, 'bar'),
(2, 3, 'bar'),
(4, 3, 'foo');

SELECT * FROM tab;

CREATE TABLE
INSERT 0 4
SELECT 4

var1	var2	var3
1	2	foo
4	5	bar
2	3	bar
4	3	foo

Basic example

In my opinion, the most popular cases for this operation are the categorisation of numerical variables and the replacement of categorical variables. So in the following cell such operations are performed.

--postgreSQL
SELECT
    CASE
        WHEN var1+var2 > 5 THEN '>5'
        ELSE '<=5'
    END num_to_cat,
    CASE
        WHEN var3='foo' THEN 'new foo'
        WHEN var3='bar' THEN 'new bar'
    END replace
FROM tab;

SELECT 4

num_to_cat	replace
<=5	new foo
>5	new bar
<=5	new bar
>5	new foo

Nested

You can use the CASE value as the result of an external CASE statement. So here is an example of how it can be used:

--postgreSQL
SELECT
    CASE
        WHEN var1+var2 > 5 THEN
            CASE
                WHEN var3='foo' THEN '>5 and foo'
                ELSE 'bar'
            END
        ELSE '<=5'
    END num_to_cat
FROM tab;

SELECT 4

num_to_cat
<=5
bar
<=5
>5 and foo