What does the NULL value mean in SQL?

The NULL value in SQL indicates the absence of data or an unknown value. It is not an empty string, numeric, or boolean value.

Key features:

• Uncertainty: NULL indicates that the value of the column is unknown or not applicable for this row.
• Comparison: Comparing with NULL (= NULL or != NULL) always returns UNKNOWN, not TRUE or FALSE. To check for NULL or the absence of NULL, the operators IS NULL and IS NOT NULL are used.
• Aggregate functions: Most aggregate functions (such as SUM(), AVG(), COUNT()) ignore NULL values. COUNT(*) counts all rows, including those where the column contains NULL.
• Constraints: NULL can be allowed or disallowed for a specific column using NULL or NOT NULL constraints when creating the table.

Example:

SELECT customer_name
FROM orders
WHERE shipping_date IS NULL; -- Selects orders with unknown shipping date

SELECT COUNT(order_id) FROM orders; -- Counts all orders
SELECT COUNT(shipping_date) FROM orders; -- Counts orders with a specified shipping date