CodeGym /Courses /SQL & Hibernate /The special null value

The special null value

SQL & Hibernate
Level 1 , Lesson 4
Available

5.1 Lack of significance

The SQL language, like the Java language, has a special keyword to denote the absence of a value - it's null. Or as it is often written - NULL.

The usage NULLin SQL is very similar to the usage of null in Java. In Java, there may be objects whose fields contain null values. In SQL, there may be tables whose rows contain NULL values.

Let me add a couple of unbranded products to our product table on purpose. If the brand of the product is unknown, then the value will be NULL.

To display such products, let's sort our product table by id in reverse order and take 10 records. The request will look like this:

SELECT * FROM product
ORDER BY id DESC
LIMIT 10

We get the following query result:

id name brand price count
15 Rivet NULL 0.01 1000
14 Screw NULL 0.10 1000
13 Stand IKEA 100.00 10
12 flowerpot Smartflower 99.00 9
eleven Armchair Bosch 44.00 8
10 Plate Bosch 199.00 10
9 Dresser IKEA 99.00 10
8 Lamp LG 15.00 100
7 TV LG 350.00 4
6 Shelf Bosch 25.00 114

Yes, I added rivets and screws to the table. They have price and quantity, but no brand.

5.2 Comparison with NULL

Do you remember that sometimes you used to get mad in Java when using null? That you can't compare variables that can refer to null values ​​via equals(). Example:

Product product1 = new Product();
Product product2 = null;
if (product2.equals(product1) {  //an exception will be raised here}

You will not believe it, but in the SQL language you are also waiting for the nuances when working with NULL values. And it sounds something like this:nothing is NULL.

If you try to filter out all products whose brand is null:

SELECT * FROM product
WHERE brand = NULL

You will get an empty table:

id name brand price count

But if you write in the query that brand is not equal to NULL, you will get the same result:

SELECT * FROM product
WHERE brand != NULL

You will again get an empty table:

id name brand price count

Any comparison/action with NULL will return NULL:

Expression Result
brand = NULL NULL
brand != NULL NULL
NULL = NULL NULL
NULL != NULL NULL

Yes,even if you compare NULL with NULL, then the answer will be NULL.

5.3 IS NULL and IS NOT NULL

So how do we check that some field (or some column) has a value of NULL? And for this, SQL has a special expression - IS NULL. It looks like this.

variable IS NULL

If you want to check if a column of your table is not equal to NULL, then you need to write IS NOT NULL:

variable IS NOT NULL

Let's write a query that will select all products from the product table whose brand is equal to NULL. Such a request would look like this:

SELECT * FROM product
WHERE brand IS NULL

And we get the following query result:

id name brand price count
14 Screw NULL 0.10 1000
15 Rivet NULL 0.01 1000

Now let's display all the products whose price is less than $20:

SELECT * FROM product
WHERE price  < 20

And we get the following query result:

id name brand price count
2 Chair IKEA 5.00 45
8 Lamp LG 15.00 100
14 Screw NULL 0.10 1000
15 Rivet NULL 0.01 1000

Now we specify that the price must be less than $20 and the brand is not NULL.

SELECT * FROM product
WHERE price  < 20 AND brand IS NOT NULL

We get the following query result:

id name brand price count
2 Chair IKEA 5.00 45
8 Lamp LG 15.00 100

Great, the last two lines are gone. Here's how it works. A little unusual, but still there is a certain logic in this.

Comments (5)
TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION
Ranganathan Kasiganesan Level 92, Chennai, India Expert
18 November 2024
It's a GYM for SQL.
Binesh Balakrishnan Level 108, Bangalore, India Expert
23 April 2024
condition in task#0149 is incorrect.
Thomas Level 13, Scottsdale, United States
12 August 2024
Level 77 ? lol Sun Microsystems architect ? or Oracle phd ?
Parsa Level 79, Bangalore, India Expert
18 January 2025
Yeah. Identifier is null.
Hoist Level 2, San Diego, United States
13 April 2024
Worthwhile section! Good practical examples in this SQL section on popular Syntax to get at data and manipulate it. CodeGym is a quirky; but very good platform )