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 = NULLYou 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 != NULLYou 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 NULLIf 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 NULLLet'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 NULLAnd 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 < 20And 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 NULLWe 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.
GO TO FULL VERSION