SQL Academy · บทเรียน

COALESCE และ NULLIF

กำหนดค่าเริ่มต้นและหลีกเลี่ยงการหารด้วยศูนย์

COALESCE และ NULLIF เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

การแทนที่ค่าที่หายไป

การตรวจหาค่า NULL เป็นเพียงครึ่งหนึ่งของงาน — บ่อยครั้งคุณต้องการแทนที่ค่าด้วยค่าเริ่มต้นที่เหมาะสม ฟังก์ชัน COALESCE ของเอสคิวแอลทำสิ่งนี้ได้โดยตรง

และเมื่อคุณต้องการสร้างค่า NULL โดยตั้งใจ (เช่น เพื่อหลีกเลี่ยงการหารด้วยศูนย์) NULLIF คือเครื่องมือที่ใช้ได้ บทเรียนนี้ครอบคลุมทั้งสองเรื่อง

-- Show a placeholder when phone is missing
SELECT name, COALESCE(phone, 'no phone on file') AS phone
FROM customers;

วิธีทำงานของ COALESCE

COALESCE รับค่าได้จำนวนเท่าใดก็ได้ และคืนค่าค่าแรกที่ไม่ใช่ NULLโดยไล่จากซ้ายไปขวา

หากค่าทุกตัวเป็น NULL ผลลัพธ์จะเป็น NULL ลองนึกภาพว่าเป็น “ใช้ค่านี้ หรือค่านั้น หรือใช้ค่าสำรองนี้เป็นลำดับสุดท้าย”

SELECT
  COALESCE(NULL, NULL, 'third', 'fourth') AS a, -- 'third'
  COALESCE(NULL, 42)                       AS b, -- 42
  COALESCE(NULL, NULL)                     AS c; -- NULL

การต่อค่าสำรอง

เนื่องจาก COALESCE รับค่าได้หลายค่า คุณจึงสร้างลำดับความต้องการได้

ตัวอย่างเช่น ให้ความสำคัญกับหมายเลขโทรศัพท์มือถือก่อน ตามด้วยหมายเลขที่ทำงาน หมายเลขบ้าน และสุดท้ายเป็นข้อความคงที่

SELECT name,
  COALESCE(mobile, work_phone, home_phone, 'unreachable') AS best_contact
FROM contacts;

ค่าเริ่มต้นในการคำนวณ

NULL แพร่ผ่านการคำนวณเลขคณิต ดังนั้นค่าที่หายไปเพียงค่าเดียวอาจทำให้การคำนวณทั้งชุดกลายเป็น NULL ใช้ COALESCE ครอบค่าข้อมูลเข้าที่อาจเป็น NULL เพื่อแทนที่ด้วยค่าที่ไม่ส่งผลต่อการคำนวณ

ในที่นี้ ส่วนลดที่หายไปจะถือเป็น 0

-- Without COALESCE, a NULL discount makes total NULL
SELECT
  price - COALESCE(discount, 0) AS total
FROM line_items;

กฎชนิดข้อมูลของ COALESCE

ค่าทั้งหมดที่ส่งให้ COALESCE ต้องมีชนิดข้อมูลที่เข้ากันได้ PostgreSQL จะเลือกชนิดข้อมูลร่วมกัน และจะรายงานข้อผิดพลาดหากไม่สามารถทำได้

ตัวอย่างเช่น คุณไม่สามารถผสมตัวเลขกับข้อความอิสระโดยไม่แปลงชนิดข้อมูลได้ ค่าสำรองก็ต้องเป็นตัวเลขเช่นกัน

-- OK: both integers
SELECT COALESCE(score, 0) FROM results;

-- Cast when mixing types
SELECT COALESCE(score::text, 'n/a') FROM results;

รู้จัก NULLIF

NULLIF(a, b) มีแนวคิดตรงข้ามกับ COALESCE: จะคืนค่า NULL เมื่อ a = b และมิฉะนั้นจะคืนค่า a

เป็นวิธีสั้นกระชับในการเปลี่ยนค่าพิเศษที่ระบุให้เป็นค่า NULL จริง

SELECT
  NULLIF(5, 5)   AS a,  -- NULL (equal)
  NULLIF(5, 9)   AS b,  -- 5    (not equal)
  NULLIF('', '') AS c;  -- NULL (treat empty string as missing)

NULLIF สำหรับการหารด้วยศูนย์

กรณีใช้งานแบบดั้งเดิมของ NULLIF คือการหลีกเลี่ยงข้อผิดพลาดจากการหาร การหารด้วย 0 จะทำให้เกิดข้อผิดพลาด แต่การหารด้วย NULL จะให้ผลเป็น NULL โดยไม่เกิดข้อผิดพลาด

ครอบตัวหารด้วย NULLIF(divisor, 0) เพื่อเปลี่ยนการหยุดทำงานให้เป็นค่า NULL ที่ปลอดภัย

-- If total_visits is 0, this would error.
-- NULLIF turns the divisor into NULL, giving a NULL ratio instead.
SELECT
  conversions / NULLIF(total_visits, 0) AS conversion_rate
FROM campaigns;

การใช้ NULLIF ร่วมกับ COALESCE

NULLIF และ COALESCE ทำงานร่วมกันได้ดี ใช้ NULLIF เพื่อเปลี่ยนค่าพิเศษให้เป็น NULL จากนั้นใช้ COALESCE เพื่อกำหนดค่าเริ่มต้นที่เหมาะสมให้กับ NULL นั้น

ในที่นี้ หมายเหตุที่เป็นข้อความว่างจะกลายเป็นข้อความคงที่ “—”

-- Treat '' as missing, then display a dash
SELECT
  COALESCE(NULLIF(note, ''), '—') AS display_note
FROM tickets;

อัตราส่วนค่าเฉลี่ยที่ปลอดภัย

นำทุกอย่างมาประกอบกันเป็นคำสั่งสืบค้นสำหรับการวิเคราะห์ที่ใช้งานได้อย่างมั่นคง: ป้องกันการหารด้วยศูนย์ด้วย NULLIF จากนั้นแสดง 0 แทน NULL ด้วย COALESCE

คำสั่งสืบค้นนี้จะไม่เกิดข้อผิดพลาด และแสดงตัวเลขที่ชัดเจนเสมอ

SELECT
  product_id,
  COALESCE(returns / NULLIF(orders, 0), 0) AS return_rate
FROM product_stats;

COALESCE กับ IS NULL

ทั้งสองอย่างจัดการค่า NULL แต่มีเป้าหมายต่างกัน:

IS NULL / IS NOT NULL — ตรวจสอบค่าที่หายไปในเงื่อนไข
COALESCE — แทนที่ค่าในผลลัพธ์

เลือกใช้ COALESCE เมื่อกำลังจัดรูปผลลัพธ์ และใช้ IS NULL เมื่อกำลังกรองข้อมูล

-- Filter (IS NULL)
SELECT * FROM customers WHERE phone IS NULL;

-- Substitute (COALESCE)
SELECT COALESCE(phone, 'unknown') FROM customers;

สรุปรวม

ข้อมูลอ้างอิงอย่างรวดเร็วสำหรับบทเรียนนี้:

COALESCE(a, b, c) → ค่าแรกที่ไม่ใช่ NULL
NULLIF(a, b) → NULL เมื่อเท่ากัน มิฉะนั้นเป็น a
ใช้ NULLIF(divisor, 0) เพื่อหลีกเลี่ยงการหารด้วยศูนย์
ใช้ COALESCE เพื่อกำหนดค่าเริ่มต้นในการคำนวณและผลลัพธ์

SELECT
  COALESCE(nickname, full_name, 'guest')       AS display_name,
  revenue / NULLIF(orders, 0)                  AS avg_order_value
FROM accounts;

ตรวจสอบความเข้าใจ

NULLIF(amount, 0) คืนค่าอะไรเมื่อ amount เป็น 0

ทบทวน

ตอนนี้คุณสามารถแทนที่ค่า NULL ด้วยค่าเริ่มต้นโดยใช้ COALESCE (ค่าแรกที่ไม่ใช่ NULL จะถูกเลือก) และสร้างค่า NULL โดยตั้งใจด้วย NULLIF (ให้ค่า NULL เมื่อค่าทั้งสองตรงกัน)

คุณได้เห็นรูปแบบการหลีกเลี่ยงการหารด้วยศูนย์และวิธีต่อฟังก์ชันทั้งสองเข้าด้วยกันเพื่อให้ได้ผลลัพธ์ที่ปลอดภัยและเรียบร้อย ต่อไปคุณจะสำรวจวิธีที่ NULL ทำงานภายใน COUNT, SUM และ JOIN

SELECT
  COALESCE(NULLIF(comment, ''), 'no comment') AS comment,
  total / NULLIF(qty, 0)                       AS unit_price
FROM invoices;

คำถามที่พบบ่อย

บทเรียน “COALESCE และ NULLIF” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “COALESCE และ NULLIF” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “COALESCE และ NULLIF”

กำหนดค่าเริ่มต้นและหลีกเลี่ยงการหารด้วยศูนย์ คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน

บทเรียน “COALESCE และ NULLIF” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ SQL Academy