MariaDB Tutorial / Compare NULL Correctly with IS NULL and COALESCE

NULL represents missing or unknown information, so use IS NULL or IS NOT NULL instead of ordinary equality comparisons to test for it. COALESCE chooses the first non-NULL expression for a result; it does not replace the stored value or skip an empty string.

Keep NULL, empty text and zero distinct

An uncollected note can be NULL, a submitted blank note can be an empty string, and known zero stock can be numeric zero. Define the policy intentionally: treating every visually blank value alike can erase the difference between missing and collected information. Define NULL acceptance at input using Define Input Rules with NULL, DEFAULT, NOT NULL, and CHECK; when exclusions depend on another query, also compare NOT EXISTS in Use Subqueries, EXISTS, and NOT EXISTS.

This exercise assumes ordinary string handling, without a compatibility mode such as EMPTY_STRING_IS_NULL. Check @@SESSION.sql_mode before generalizing behavior to another installation.

Check the MariaDB documentation for the syntax and conditions used here: null values, coalesce.

Prepare both missing and empty notes

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_16_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_16_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p16_price CHECK (price >= 0),
  CONSTRAINT ck_p16_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_16_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

SELECT product_id, note, note IS NULL AS is_missing,
       note = '' AS is_empty
FROM products_16_lab ORDER BY product_id;

Comparing a NULL note with an empty string produces NULL, not zero. Think in terms of true, false and unknown when reasoning about negation and WHERE; treating unknown as an ordinary false value leads to incorrect conclusions.

Select missing values explicitly

SELECT product_id FROM products_16_lab
WHERE note = NULL ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NULL ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NOT NULL ORDER BY product_id;

The equality-to-NULL query returns no rows. IS NULL returns IDs 1 and 4; IS NOT NULL returns 2, 3, 5 and 6, including the empty strings. A non-NULL note is not necessarily a note containing visible text.

SELECT product_id FROM products_16_lab
WHERE note <> '선물 포장' ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NULL OR note <> '선물 포장' ORDER BY product_id;

The first not-equal comparison selects IDs 2, 5 and 6. NULL notes at 1 and 4 are excluded because their comparison is unknown. The explicit IS NULL alternative includes them and returns 1, 2, 4, 5 and 6. Decide whether missing inputs belong in the result rather than assuming they are the opposite of a matching value.

Choose display fallbacks without rewriting data

SELECT product_id, COALESCE(note, '미입력') AS display_note
FROM products_16_lab ORDER BY product_id;
SELECT COALESCE(NULL, NULL, '기본 안내') AS fallback_text;
SELECT NULL = NULL AS ordinary_equal,
       NULL <=> NULL AS null_safe_equal;

The first query displays a Korean missing-value label for IDs 1 and 4 but leaves IDs 2 and 5 empty. The second returns the supplied default-guidance string because all earlier candidates are NULL. COALESCE examines candidates from left to right and returns NULL if all are NULL.

The final comparison yields ordinary_equal=NULL and null_safe_equal=1. MariaDB’s <=> operator can test equality even when both operands are NULL; IS NULL is clearer for a simple missing-column test. Keep fallback types appropriate to their purpose instead of mixing a numeric stock quantity and a textual label into later calculations. Requerying the source notes shows that COALESCE has not updated them.

Recognize NULL inside a NOT IN list

SELECT product_id FROM products_16_lab
WHERE category NOT IN ('문구', NULL) ORDER BY product_id;

This exclusion returns no rows. The stationery category matches an excluded value; for other categories, the comparison involving NULL remains unknown. Removing NULL from the list selects the household and electronics rows, IDs 3, 4 and 6.

Validate lists supplied by applications or subqueries. NOT EXISTS can express an absence test across tables, but it is not a context-free textual replacement for NOT IN. Also decide how NULL in the compared column itself should be treated; the EXISTS and subquery guide expands that distinction.

Review the data policy before filling missing values

A prettier display does not make incomplete data complete. Displaying unknown stock as zero may incorrectly imply that an item is sold out. Use a missing-value label or separate status when appropriate, and use DEFAULT and NOT NULL only for the input policies they actually represent. The default and constraint discussion provides that context. If a client hides NULL as a blank, include an IS NULL result column instead of guessing from appearance.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_16_lab;