The SQL Null ambiguity

Photo by Michał Parzuchowski on Unsplash

Dealing with null in SQL can get tricky if you think like a programmer/developer. Why is it so? Let me you give you an example.

Let’s cover the example from programming point of view first then we’ll jump into the SQL world.

Let’s talk about data, we have four products, out of which two has been launched into the market and two are waiting to get launched. This is how it looks like.

const products = [
    { "id": 1, "name": "P1", "launchYear": 2019 },
    { "id": 2, "name": "P2", "launchYear": 2021 },
    { "id": 3, "name": "P3", "launchYear": null},
    { "id": 4, "name": "P4", "launchYear": null}
];

Now get a list of products except those launched in the year 2019.

products.filter(product => product.launchYear !== 2019) 
// 3 products
products.filter(product => 
   product.launchYear !== 2019 || product.launchYear === null
)
// 3 products

Let’s cover the same scenario with SQL now. Create the products table with the same data as defined above.

CREATE TABLE products (
    id INT,
    name VARCHAR(255),
    launch_year INT
);
INSERT INTO products(id, name, launch_year)
VALUES (1,"P1", 2019),
(2, "P2", 2020),
(3, "P3", null),
(4, "P4", null);

Products table

Let’s run the following queries one by one. Both queries will produce the same result.

// First Query
SELECT 
    id, 
    name,
    launch_year
FROM products
where launch_year != "2019;
// Second Query
SELECT 
    id, 
    name,
    launch_year
FROM products
where launch_year != 2019 OR launch_year = null;

As a developer you would be thinking that we’ll get 3 products after running these queries. But no, we’ll get only one product.

Product

I am not sure about you but when I faced this situation I just got surprised and curious as well. According to my thought process null should behave the same as it was behaving in programming language. But that was not the case and that’s where I got deceived.

Why this happened?

So in relation database model, null value indicates an unknown value.
This unknown value is not equivalent to a zero value or a field that contains spaces.
Due to this nature of the null values, it is not possible to use traditional comparison (=, <, > and <>) operators in the queries. That’s why anything you evaluate/compute with NULL results into unknown.

IS NULL

So what’s the solution here? SQL has a special operator to deal with missing values, IS NULL
Specifically SQL is not comparing values here, but rather it seeks out missing values.

SELECT 
    id, 
    name,
    launch_year
FROM products
WHERE launch_year != 2019 OR launch_year IS NULL;

Above query will yield the desired result.

That’s it. I hope you’ve learned something today. Thank you!!