MySQL ON NULL & EI OLE NULL koos näidetega

Autor: Marcus Allen Marcus Allen
Hours Ajakohastatud

⚡ Nutikas kokkuvõte

MySQL IS NULL and IS NOT NULL are comparison keywords that test whether a column holds a missing value. NULL marks absent data, behaves differently from zero or an empty string, and requires dedicated operators for reliable filtering.

  • 🧩 Põhidefinitsioon: NULL is a placeholder for data that does not exist. It is not a data type, and it is not the number zero.
  • ➗ Arithmetic Behaviour: Any arithmetic expression that involves NULL returns NULL, so 69 + NULL evaluates to NULL rather than to 69.
  • 📊 Aggregate Impact: COUNT(column) and other aggregate functions skip NULL rows, while COUNT(*) still counts every row in the table.
  • 🚫 NOT NULL Constraint: Declaring a column NOT NULL rejects any insert that omits a value, which protects mandatory fields such as identifiers.
  • 🔍 Correct Filtering: IS NULL and IS NOT NULL are the only reliable tests, because the equality operator never matches a NULL value.
  • 🇧🇷 Three-Valued Logic: Comparisons against NULL return UNKNOWN, so SELECT NULL = NULL produces NULL instead of TRUE.
Loe rohkem

MySQL IS NULL and IS NOT NULL

In SQL, NULL is both a value and a keyword. Let us look into the NULL value first.

MySQL ON NULL JA EI OLE NULL

What is NULL in MySQL?

Lihtsamalt öeldes NULL is a place holder for data that does not exist. When performing insert operations on tables, there will be times when some field values are not available.

Tõeliste relatsiooniliste andmebaaside haldussüsteemide nõuete täitmiseks MySQL uses NULL as the place holder for the values that have not been submitted. The screenshot below shows how NULL values look in a database table.

Null väärtusena

Notice that the empty cells are marked NULL, not blank text and not zero. Before going further, look at some of the basics of NULL.

  • NULL ei ole andmetüüp – see tähendab, et seda ei tuvastata kui "int", "date" või mis tahes muud määratletud andmetüüpi.
  • Aritmeetilised toimingud kaasates NULL alati tagasta NULL, for example, 69 + NULL = NULL.
  • sild koondfunktsioonid ignore rows that hold NULL values. The one exception is COUNT(*), which counts every row regardless of NULL.

How Aggregate Functions Treat NULL

This rule changes the answers that reporting queries return, so let us prove it. Start with the current contents of the members table.

SELECT * FROM `members`;

Ülaltoodud skripti täitmine annab meile järgmised tulemused.





membership_ number
full_ names
gender
date_of_ birth
physical_ address
postal_ address
contact_ number
email





1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm




2
Janet Smith Jones
Female
23-06-1980
Melrose 123
NULL
NULL
jj@fstreet.com




3
Robert Phil
Male
12-07-1989
3rd Street 34
NULL
12345rm@tstreet.com


 

 4
 Gloria Williams
 Female
 14-02-1984
 2nd Street 23
 NULL
 NULL
 NULL
 

 

 5
 Leonard Hofstadter
 MaleNULL
 Woodcrest
 NULL
 845738767
 NULL
 

 

 6
 Sheldon Cooper
 Male
 NULL
 Woodcrest
 NULL
 976736763
 NULL
 

 

 7
 Rajesh Koothrappali
 Male
 NULL
 Woodcrest
 NULL
 938867763
 NULL
 

 

 8
 Leslie Winkle
 Male
 14-02-1984
 Woodcrest
 NULL
 987636553
 NULL
 

 

 9
 Howard Wolowitz
 Male
 24-08-1981
 SouthPark
 P.O. Box 4563
 987786553
 lwolowitz[at]email.me

The highlighted contact_ number column holds nine rows in total, but two of them are NULL. Let us count all members who have updated their contact number.

SELECT COUNT(contact_number) FROM `members`;

Ülaltoodud päringu täitmine annab meile järgmised tulemused.





COUNT(contact_number)






7

Märge: the answer is 7 and not 9, because the two NULL values were not included. Running COUNT(*) on the same table would return 9, since COUNT(*) counts rows rather than values.

SEOTUD ARTIKLID

NOT NULL Väärtused

A safer approach is to stop NULL from entering mandatory columns at all. That is the job of the NOT NULL constraint.

What is the NOT Operator?

The NOT logical operator is used to test Boolean conditions, and it returns true if the condition is false. The NOT operator returns false if the condition being tested is true.

Tingimus EI Operator Tulemus
Tõsi Vale
Vale Tõsi

Why use NOT NULL?

There will be cases when we have to perform computations on a query result set and return the values. Performing any arithmetic operation on a column that holds a NULL value returns a NULL result. In order to avoid such situations, we can employ the NOT NULL clause to limit the results on which our data operates.

Creating a Table with a NOT NULL Column

Let us suppose that we want to create a table with certain fields that should always be supplied with values when inserting new rows. We can use the NOT NULL clause on a given field when creating the table.

The example shown below creates a new table that contains employee data. The employee number should always be supplied.

CREATE TABLE `employees`(
  employee_number int NOT NULL,
  full_names varchar(255) ,
  gender varchar(6)
);

Let us now try to insert a new record without specifying the employee number and see what happens.

INSERT INTO `employees` (full_names,gender) VALUES ('Steve Jobs', 'Male');

Ülaltoodud skripti käivitamine MySQL Workbench gives the following error, because the mandatory column was left out.

NOT NULL Väärtused

IS NULL and IS NOT NULL Keywords

The constraint blocks new NULL values. To work with NULL values that already exist, NULL is used as a keyword. The syntax is as follows.

column_name IS NULL
column_name IS NOT NULL

SIIN

  • "ON NULL" on märksõna, mis teostab Boole'i ​​võrdlust. Tagastab tõene, kui esitatud väärtus on NULL, ja false, kui esitatud väärtus ei ole NULL.
  • “IS NOT NULL” is the keyword that performs the opposite comparison. It returns true if the supplied value is not NULL and false if the supplied value is NULL.

Let us look at a practical example that uses the IS NOT NULL keyword to eliminate all the rows that hold NULL values in a column.

Continuing with the members table above, suppose we need the details of members whose contact number is not NULL. We can execute a query like this.

SELECT * FROM `members` WHERE contact_number IS NOT NULL;

Executing the above query returns only the seven records where the contact number is present, which matches the COUNT result from the previous section.

Now suppose we want the opposite: the member records where the contact number is missing. We can use the following query.

SELECT * FROM `members` WHERE contact_number IS NULL;

Executing the above query gives the two member records whose contact number is NULL.


 
 

 membership_ number
 full_names
 gender
 date_of_birth
 physical_address
 postal_address
 contact_ number
 email
 

 
 
 

 2
 Janet Smith Jones
 Female
 23-06-1980
 Melrose 123
 NULL
 NULL
 jj@fstreet.com
 

 

 4
 Gloria Williams
 Female
 14-02-1984
 2nd Street 23
 NULL
 NULL
 NULL

Hoiatus: a condition such as WHERE contact_number = NULL returns an empty result set, even though NULL values exist. The equality operator can never match NULL, so IS NULL is the only correct test.

Comparing NULL Values with Three-Valued Logic

Kolme väärtusega loogika – performing Boolean operations on conditions that involve NULL can return “Unknown”, “True” or “False”.

Using the “IS NULL” keyword võrdlustoimingute tegemisel kaasates NULL Tulu tõsi or vale. Using the other comparison operators returns “Unknown” (NULL). The table below compares each expression side by side.

Väljendus Tulemus Tähendus
SELECT 5 = 5; 1 TRUE
SELECT NULL = NULL; NULL UNKNOWN
SELECT 5 > 5; 0 FALSE
SELECT NULL > NULL; NULL UNKNOWN
SELECT 5 IS NULL; 0 FALSE
SELECT NULL IS NULL; 1 TRUE

Compare the number five with itself, then repeat the operation with NULL.

SELECT 5 =5;
SELECT NULL = NULL;




5 =5
NULL = NULL






1
NULL

The first result is 1 (TRUE). The second is NULL, because MySQL cannot state that one unknown value equals another unknown value. Now use the IS NULL keyword on the same values.

SELECT 5 IS NULL;
SELECT NULL IS NULL;




5 IS NULL
NULL IS NULL






0
1

This time the answers are definite: 0 (FALSE) and 1 (TRUE). Only the IS NULL and IS NOT NULL keywords return a definite answer when NULL is involved.

KKK

Zero is a number and an empty string is text, so both match equality tests. NULL means no value was supplied at all, which is why it responds only to IS NULL and IS NOT NULL.

IFNULL(column, ‘N/A’) returns the substitute whenever the column is NULL. COALESCE(a, b, c) returns the first argument that is not NULL. Both are useful inside MySQL funktsioonid ja aruanded.

Ei. MySQL applies NOT NULL to every primary key column automatically, because a key that identifies a row cannot be missing. A UNIQUE index is different and does permit multiple NULL values.

Usually, yes. AI assistants inside tools such as MySQL Workbench turn “members with no phone number” into a WHERE column IS NULL clause. Review the filter, because an equality test against NULL silently returns nothing.

Often, yes. AI review tools flag faults such as = NULL comparisons, a SELECT that averages a column holding NULL, and NOT IN lists containing NULL. The final judgement belongs to the person who knows the data.

Võta see postitus kokku järgmiselt: