WHERE + NULL three-valued logic — vì sao age != 30 không trả NULL row

-- Row co assignee_id = NULL khong duoc tra ve
SELECT * FROM tasks WHERE assignee_id != 5;
-- Vi sao: NULL != 5 -> UNKNOWN -> WHERE loai row nay

-- Kiem tra: co bao nhieu task chua assigned?
SELECT COUNT(*) FROM tasks WHERE assignee_id IS NULL;
-- Dung IS NULL thay vi = NULL

-- WRONG: khong tra ve gi ca
SELECT col FROM t WHERE col = NULL;
-- Ly do: NULL = NULL -> UNKNOWN -> WHERE loai

-- RIGHT: IS NULL tra ve TRUE khi col la NULL
SELECT col FROM t WHERE col IS NULL;

-- Kiem tra nhanh trong psql
SELECT NULL = NULL;    -- NULL (UNKNOWN)
SELECT NULL IS NULL;   -- TRUE

SELECT NULL != NULL;   -- NULL (UNKNOWN)
SELECT NULL IS NOT NULL; -- FALSE

-- Table users co 100 row, id tu 1 den 100
-- Tat ca id deu != 1, != 2, != NULL... hay khong?
SELECT * FROM users WHERE id NOT IN (1, 2, NULL);
-- Tra ve 0 row! Du table co 100 row.

WHERE id != 1 AND id != 2 AND id != NULL
-- id != NULL -> UNKNOWN
-- (TRUE) AND (TRUE) AND UNKNOWN -> UNKNOWN
-- WHERE loai row co UNKNOWN -> loai moi row

-- Fix 1: loc NULL ra khoi subquery truoc
SELECT * FROM users WHERE id NOT IN (1, 2);

-- Fix 2: dung NOT EXISTS thay NOT IN
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES (1), (2), (NULL)) AS sub(v)
  WHERE sub.v = u.id
);
-- NOT EXISTS an toan voi NULL vi su dung = (khong IN)

-- Fix 3: filter NULL truoc khi subquery tra ve
SELECT * FROM users
WHERE id NOT IN (
  SELECT id FROM blacklist WHERE id IS NOT NULL
);

-- TaskFlow: dem task theo trang thai assignment
SELECT
  COUNT(*)                      AS total_rows,
  COUNT(assignee_id)            AS assigned_rows,
  COUNT(DISTINCT assignee_id)   AS unique_assignees
FROM tasks;

-- Ket qua vi du:
-- total_rows=500, assigned_rows=380, unique_assignees=12
-- 120 task co assignee_id = NULL -> COUNT(assignee_id) bo qua

-- Vi du minh hoa su khac biet
SELECT
  AVG(score)                AS avg_with_null_skipped,
  SUM(score) / COUNT(*)     AS avg_counting_nulls_as_zero
FROM student_results;
-- Neu 3/10 row co score = NULL:
-- avg_with_null_skipped: tinh tren 7 row
-- avg_counting_nulls_as_zero: chia cho 10 -> ket qua thap hon

-- PostgreSQL: || operator
SELECT 'Hello, ' || NULL;       -- NULL (khong phai 'Hello, ')
SELECT 'Hello, ' || name        -- NULL neu name la NULL
FROM users WHERE name IS NOT NULL;  -- OK nhung phai loc truoc

-- Fix: COALESCE thay the NULL bang default value
SELECT 'Hello, ' || COALESCE(name, 'Friend') FROM users;
-- name = NULL -> 'Hello, Friend'
-- name = 'Alice' -> 'Hello, Alice'

-- MySQL: CONCAT() co behavior giong PG -- tra NULL neu co NULL
SELECT CONCAT('Hello, ', NULL);          -- NULL

-- MySQL: CONCAT_WS() skip NULL thay vi cho NULL
SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob');  -- 'Alice, Bob'
-- CONCAT_WS = Concat With Separator, separator la arg dau tien

-- tasks.assignee_id = NULL khong match voi user nao
SELECT t.id, t.title, u.name
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id;
-- Task co assignee_id = NULL: u.name = NULL (khong match)
-- Day la dung behavior voi LEFT JOIN -- nhung can biet

-- INNER JOIN: task voi assignee_id = NULL bi loai
SELECT t.id, t.title, u.name
FROM tasks t
INNER JOIN users u ON t.assignee_id = u.id;
-- Unassigned task khong xuat hien trong ket qua

-- IS NOT DISTINCT FROM: NULL-safe equality (PostgreSQL)
SELECT * FROM tasks t
LEFT JOIN users u ON t.assignee_id IS NOT DISTINCT FROM u.id;
-- NULL IS NOT DISTINCT FROM NULL -> TRUE -> match

-- IS DISTINCT FROM: nguoc lai cua IS NOT DISTINCT FROM
SELECT * FROM tasks WHERE assignee_id IS DISTINCT FROM 5;
-- Tra ve ca row co assignee_id != 5 VA assignee_id = NULL

-- Pattern 1: kiem tra NULL dung cach
WHERE col IS NULL
WHERE col IS NOT NULL

-- Pattern 2: include NULL row khi filter !=
-- Thay vi: WHERE assignee_id != 5
-- Dung:
WHERE assignee_id != 5 OR assignee_id IS NULL

-- Pattern 3: COALESCE cho default value trong predicate
WHERE COALESCE(assignee_id, -1) != 5
-- NULL duoc treat nhu -1, nen -1 != 5 -> TRUE -> pass

-- Pattern 4: IS NOT DISTINCT FROM cho NULL-safe comparison (PG)
WHERE col IS NOT DISTINCT FROM other_col
-- Equivalent = nhung an toan voi NULL

-- Tao column voi NOT NULL constraint
ALTER TABLE tasks ADD COLUMN priority INT NOT NULL DEFAULT 3;
-- Insert thieu priority -> ERROR, khong insert duoc

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
-- Insert khong co status -> status = 'active' tu dong
-- Khong bao gio co NULL trong column nay

SELECT COALESCE(assignee_id, 0) AS assignee_or_default FROM tasks;
-- assignee_id = NULL -> tra ve 0
-- Data trong table van la NULL

// KHONG tra ve user co age = null
const users = await prisma.user.findMany({
  where: { age: { not: 30 } }
});
// Generate: SELECT ... WHERE age != 30
// NULL row bi loai -- giong SQL

// Fix: explicit OR cho NULL
const users = await prisma.user.findMany({
  where: {
    OR: [
      { age: { not: 30 } },
      { age: null }
    ]
  }
});
// Generate: WHERE age != 30 OR age IS NULL
// Bao gom ca row co age = null

-- WRONG: bo qua unassigned task (assignee_id = NULL)
SELECT id, title, assignee_id
FROM tasks
WHERE assignee_id != 5;
-- NULL row bi loai vi NULL != 5 -> UNKNOWN -> WHERE loai

-- RIGHT: explicit NULL handling voi OR
SELECT id, title, assignee_id
FROM tasks
WHERE assignee_id != 5 OR assignee_id IS NULL;
-- Bao gom ca task chua assigned

-- ALT: COALESCE treat NULL nhu sentinel -1
SELECT id, title, assignee_id
FROM tasks
WHERE COALESCE(assignee_id, -1) != 5;
-- NULL -> COALESCE(NULL, -1) = -1 -> -1 != 5 -> TRUE -> pass
-- Luu y: chi dung -1 neu khong co user nao co id = -1

-- Kiem tra: bao nhieu task bi WRONG query bo qua
SELECT COUNT(*) FROM tasks WHERE assignee_id IS NULL;
-- Ket qua nay = so task bi mat trong WRONG query