NULL, DISTINCT & CASE

Three-valued logic

SQL has TRUE, FALSE and UNKNOWN. Any comparison with NULL returns UNKNOWN, and rows are only returned when the condition is TRUE.

SELECT NULL = NULL;        -- NULL, not 1
SELECT NULL <=> NULL;      -- 1  (NULL-safe)
SELECT 5 + NULL;           -- NULL — NULL poisons arithmetic
SELECT CONCAT('a', NULL);  -- NULL

NULL functions

Function	Returns
IFNULL(a, b)	a if not null, else b
COALESCE(a,b,c,…)	First non-null argument
NULLIF(a, b)	NULL if a = b, else a
ISNULL(a)	1 if a is null

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

NULL in aggregates

Aggregate functions skip NULL — except COUNT(*):

SELECT
  COUNT(*)      AS all_rows,     -- counts every row
  COUNT(email)  AS with_email,   -- skips NULLs
  AVG(cgpa)     AS avg_cgpa      -- average of non-null values only
FROM students;

That difference matters: AVG over a column with NULLs divides by the count of non-null values, not by the row count.

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 whole select list, not just the first column. It also requires a sort or hash, so on large tables prefer GROUP BY with an index, or fix the duplicate-producing join instead.

CASE expressions

-- searched CASE
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 CASE
SELECT name_en,
  CASE department
    WHEN 'CSE' THEN 'Computer Science'
    WHEN 'EEE' THEN 'Electrical'
    ELSE 'Other'
  END AS dept_full
FROM students;

Pivoting with CASE

SELECT
  department,
  SUM(CASE WHEN cgpa >= 3.5 THEN 1 ELSE 0 END) AS excellent,
  SUM(CASE WHEN cgpa <  2.5 THEN 1 ELSE 0 END) AS at_risk
FROM students
GROUP BY department;

This SUM(CASE …) pattern is how you build pivot-style reports in MySQL, which has no PIVOT keyword.