When working with SQL databases, you often encounter situations where some information is missing or not applicable. In SQL, such missing or unknown data is represented by a special marker called NULL. Unlike zero for numbers or an empty string for text, NULL means that the value is simply not known or does not exist for that particular row and column. It is not a value itself, but rather an indicator that the value is absent.

-- Creating a table with nullable columns for different data types
CREATE TABLE employee_info (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    name VARCHAR(100),
    joined_at DATE
);

In the employee_info table above, the columns age, name, and joined_at are all allowed to be NULL. This means that when you insert data, you are not required to provide a value for every column. If you do not specify a value, or if you explicitly use NULL, SQL will store the absence of information for that field.

Handling NULL values is important because they behave differently from regular data in queries and calculations. For example:

• If you try to perform arithmetic with a NULL value, the result will also be NULL;
• Comparing a column to NULL using the usual equality operator (=) does not work as expected, because NULL is not equal to anything, not even to another NULL;
• You must use IS NULL or IS NOT NULL in your conditions.

When retrieving or manipulating data, you need to be aware that NULL values can affect the results. For instance:

• Aggregate functions like SUM or AVG ignore NULL values;
• COUNT(*) includes all rows, and COUNT(column) only counts rows where the column is not NULL.

-- Inserting rows with NULL values in different columns
INSERT INTO employee_info (age, name, joined_at) VALUES
    (25, 'Alice', '2020-01-15'),
    (NULL, 'Bob', '2019-07-01'),
    (30, NULL, NULL),
    (NULL, NULL, NULL);

In the example above, you see how NULL can be used for any data type, whether it is numeric, character, or date. The second row leaves age unknown, the third row omits both name and joined_at, and the fourth row omits all optional values. This flexibility is essential for real-world databases, where not all information is always available.

Understanding how NULL interacts with different data types and queries helps you write more accurate and reliable SQL code, ensuring that missing data is handled correctly and does not lead to unexpected results.