GET ON YOUR CASE -POSTGRESQL

The CASE expression in PostgreSQL is equivalent to the IF/ELSE statement in other programming languages. It enables you to add if-else logic to the query, resulting in a more powerful query.

Because CASE is an expression, it can be used anywhere an expression can be used, such as the SELECT, WHERE, GROUP BY, and HAVING clauses.

If you prefer watching check here:

The CASE expression comes in two flavors: general and simple.

1) General PostgreSQL?CASE?expression

CASE 
      WHEN condition_1  THEN result_1
      WHEN condition_2  THEN result_2
      [WHEN ...]
      [ELSE else_result]
END        

Each condition (condition 1, condition 2,...) in this syntax is a boolean expression that returns true or false.

When a condition evaluates to false, the CASE expression moves on to the next condition, evaluating it from top to bottom until it finds one that evaluates to true.

If a condition evaluates to true, the CASE expression returns the result that comes after the condition. For example, if condition 2 is true, the CASE expression returns result 2. It also immediately stops evaluating the following expression.

If all of the conditions are false, the CASE expression returns the result (else result) that comes after the ELSE keyword. If the ELSE clause is not present, the CASE expression returns NULL.

EXAMPLE 1: GENERAL CASE USING A TABLE EMPLOYEES

SELECT first_name,
salary,
department,
CASE?
	WHEN salary > 0 AND salary <= 100000 THEN 'underpaid'
	WHEN salary > 100000 AND salary <= 150000 THEN 'moderately paid'
	WHEN salary > 150000 AND salary <= 250000 THEN 'well paid'
	WHEN salary > 250000 THEN 'Comfortable salary'
	END level_of_salary
FROM employees
ORDER BY first_name;        

It's worth noting that we added a column alias level_of_salary ?after the CASE expression.

OUTPUT

2) Simple PostgreSQL?CASE?expression

SYNTAX

CASE expression
   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2 
   [WHEN ...]
ELSE
   else_result
END        

The CASE first evaluates the expression and sequentially compares the result with each value (value 1, value 2,...) in the WHEN clauses until it finds a match.

The CASE returns the corresponding result in the THEN clause once the result of the expression equals a value (value1, value2, etc.) in a WHEN clause.

If no matches are found, CASE returns the else result in that follows the ELSE, or NULL if the ELSE is not available.

EXAMPLE : Simple expression

SELECT first_name,
	salary,
	CASE salary > 0
		WHEN salary > 0 AND salary <=100000 THEN 'junior developer'
		WHEN salary > 100000 AND salary <= 200000 THEN 'senior Developer'
		WHEN salary > 200000 THEN 'C suite Executive'
		END job_description
FROM employees
ORDER BY first_name;        

OUTPUT

In this example we compare the salaries of the employees to know which level in the organization they are in.

CONCLUSION

I hope you learned something go CASE your way out of that SQL problem!