Boolean Expressions in SQL Select list

Employing the CASE statement

The CASE statement is a mighty tool in SQL, delivering versatile options for data transformation as well as the implementation of business logic. If you fancy a programming relation, think of simple if-else logic. CASE is capable of handling multiple conditions and returning variable values, thus paving the way for effective unit testing or scenario simulations in your datasets.

Multivariate CASE scenario:

SELECT 
  username, 
  CASE 
    WHEN score >= 90 THEN 'A Grade' --Einstein rising
    WHEN score >= 75 THEN 'B Grade' -- A for effort
    WHEN score >= 50 THEN 'C Grade' -- Mediocrity at its best!
    ELSE 'F Grade'  -- Better luck next time!
  END AS performance
FROM students;


For SQL Server users looking for concision, try the IIF function for clear and straightforward boolean evaluations:

SELECT username, IIF(score >= 50, 'Pass', 'Fail') AS result FROM students;


Preventing errors with CASE

"Equality may be the buzzword in living, but in SQL, using the equals (=) sign for comparison in the SELECT clause is frowned upon", I half-expected Gandalf to say. Errors happen when you do that. Your best friends here are CASE or IIF statements, which are the correct interpreters of your SQL logic to the DBMS.

Evaluating complex conditions with CASE

CASE can handle complex conditions, such as in this example where it checks multiple conditions within a column:

SELECT 
  product_name,
  CASE 
    WHEN quantity > 50 AND price < 20 THEN 'Bulk Bargain'
    WHEN quantity <= 50 AND price < 20 THEN 'Single Saving'
    WHEN price >= 20 THEN 'Luxury''
   ELSE 'Everyday Item'      -- Because not everything is fancy
  END AS category
FROM products;


Understanding the WHERE Clause

There's a clear distinction between filtering rows and displaying boolean expressions. The WHERE clause is the bouncer outside the club, deciding who can enter (gets selected):

SELECT id, name
FROM users
WHERE age > 18;    -- No ID, no entry!


Conversely, using a boolean expression in the SELECT list is akin to encoding info about each guest (row) based on their attributes:

SELECT id, name, (age > 18) AS is_adult
FROM users;
