IS NULL وIS NOT NULL وCOALESCE

التحقق من NULL

SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

لماذا يفشل = NULL بصمت

SELECT * FROM users WHERE deleted_at = NULL;
-- → returns 0 rows, always

COALESCE: أول قيمة غير NULL

SELECT id,
       COALESCE(nickname, full_name, email, 'Anonymous') AS display_name
FROM users;

استخدام COALESCE في WHERE

-- Treat NULL last_login_at as the epoch:
SELECT * FROM users
ORDER BY COALESCE(last_login_at, '1970-01-01') DESC;

NULLIF: العكس

-- Treat 0 as missing so AVG ignores it:
SELECT AVG(NULLIF(price, 0)) FROM products;

-- Convert empty string to NULL on insert:
INSERT INTO contacts (phone) VALUES (NULLIF(:phone, ''));

استخدام CASE للقيم الافتراضية الشرطية

SELECT id,
  CASE
    WHEN status IS NULL OR status = '' THEN 'unknown'
    WHEN status = 'A' THEN 'active'
    ELSE status
  END AS status_label
FROM users;

NULL في CHECK والقيود

CREATE TABLE prices (
  amount NUMERIC(10,2),
  CHECK (amount > 0)        -- amount = NULL passes!
);

-- Better:
CREATE TABLE prices2 (
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0)
);

NULL في JOIN ON

SELECT * FROM a JOIN b ON a.key IS NOT DISTINCT FROM b.key;

قيم NULL وUNIQUE

CREATE TABLE invites (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);
INSERT INTO invites (email) VALUES (NULL), (NULL); -- both succeed!

مراجعة القيم NULL والدوال التجميعية

SELECT COUNT(*)            FROM users;  -- all rows
SELECT COUNT(email)        FROM users;  -- rows where email IS NOT NULL
SELECT COUNT(DISTINCT id)  FROM users;

صرّحوا دائمًا بـ NOT NULL متى أمكن

مراجعة

تحقق سريع