NULL, CASE & Conditional Expressions

Three-valued logic

SELECT NULL = NULL;                      -- NULL, not true
SELECT NULL IS NOT DISTINCT FROM NULL;   -- true
SELECT 5 + NULL;                         -- NULL
SELECT 'a' || NULL;                      -- NULL
SELECT concat('a', NULL);                -- 'a'

A row is returned only when the WHERE condition evaluates to TRUE, never when it is NULL.

NULL functions

Function	Returns
coalesce(a, b, …)	The first non-null argument
nullif(a, b)	NULL if a = b, else a
greatest / least	Largest / smallest, ignoring nulls

SELECT
  name_en,
  coalesce(name_bn, name_en, 'Unknown') AS display_name,
  coalesce(email, 'not provided')       AS contact,
  nullif(cgpa, 0)                       AS cgpa_or_null
FROM students;

CASE

-- searched form
SELECT name_en, cgpa,
  CASE
    WHEN cgpa >= 3.75 THEN 'A+'
    WHEN cgpa >= 3.50 THEN 'A'
    WHEN cgpa >= 3.00 THEN 'B'
    ELSE 'Needs improvement'
  END AS grade
FROM students;

-- simple form
SELECT name_en,
  CASE department
    WHEN 'CSE' THEN 'Computer Science'
    WHEN 'EEE' THEN 'Electrical'
    ELSE 'Other'
  END AS dept_full
FROM students;

CASE stops at the first matching branch, and without ELSE it returns NULL.

FILTER: better than SUM(CASE …)

Postgres has a dedicated clause for conditional aggregation, which is far clearer than the SUM(CASE …) trick other databases need:

SELECT
  department,
  count(*)                                AS total,
  count(*) FILTER (WHERE cgpa >= 3.5)     AS excellent,
  count(*) FILTER (WHERE cgpa <  2.5)     AS at_risk,
  avg(cgpa) FILTER (WHERE email IS NOT NULL) AS avg_with_email
FROM students
GROUP BY department;

Pivoting

SELECT
  department,
  count(*) FILTER (WHERE extract(year from admitted_on) = 2024) AS y2024,
  count(*) FILTER (WHERE extract(year from admitted_on) = 2025) AS y2025
FROM students
GROUP BY department;

The tablefunc extension also provides a crosstab() function for true dynamic pivots.

DISTINCT

SELECT DISTINCT department FROM students;
SELECT DISTINCT department, admitted_on FROM students;   -- distinct combinations
SELECT count(DISTINCT department) FROM students;

DISTINCT applies to the entire select list. On large tables GROUP BY with a supporting index is often planned better, and duplicates usually mean a join is producing extra rows — fixing that is better than hiding it.

Boolean helpers

SELECT bool_and(cgpa > 2.0) AS all_passing,
       bool_or(cgpa >= 3.9) AS any_outstanding
FROM students;

SELECT (cgpa >= 3.5) AS is_excellent FROM students;   -- booleans are real values