Zero in sql

SELECT * FROM `members`;

Executing the above script gives us the following results

Let's count all members who have updated their contact_number

SELECT COUNT(contact_number) FROM `members`;

Running the above query gives us the following results.

Note: values ​​that are NULL were not included

What is not?

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

There will be times when we will need to perform calculations on the result set of a query and return values. Performing any arithmetic operations on columns that are NULL returns null results. To avoid situations like this, we can use the NOT NULL clause to limit the results our data works with.

NOT NULL values

Let's assume that we want to create a table with certain fields that must always be provided with values ​​when new rows are inserted into the table. We can use the NOT NULL clause for a given field when creating a table.

The following example creates a new table containing employee data. Employee number must always be specified

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

Let's now try inserting a new record without specifying the employee's name and see what happens.

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

Running the above script in MySQL Workbench gives the following error -

NULL keywords

NULL can also be used as a keyword when performing boolean operations on values ​​that include NULL. The keyword "IS / NOT" is used in conjunction with the word NULL for such purposes. The basic syntax when null is used as a keyword is as follows

`comlumn_name' IS NULL
`comlumn_name' NOT NULL

HERE

• "IS NULL" is a keyword that performs a boolean comparison. It returns true if the provided value is NULL and false if the provided value is not NULL.
• "NOT NULL" is a keyword that performs a boolean comparison. It returns true if the provided value is not NULL and false if the provided value is null.

Now let's look at a practical example that uses the NOT NULL keyword to exclude all column values ​​that have null values.

Continuing with the above example, let's say we want data on members whose contact number is not null. We can query like

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

Running the above query only returns records where the contact number is not null.

Let's say we want member records where the contact number is null. We can use the following query

SELECT * FROM `members` WHERE contact_number IS NULL;

Running the above query gives the details of the member whose contact number is INVALID

Zero value comparison s

Three-valued logic - performing logical operations in conditions that use NULL can return "Unknown", "True", or "False".

For example, using the keyword "IS NULL" when performing comparison operations using NULL can return true or false . Using other comparison operators returns "Unknown" (NULL).

Suppose you are comparing number five with 5

SELECT5=5;

Query result is 1 which means TRUE

Let's do the same operation with NULL

SELECT NULL = NULL;

Let's look at another example

SELECT 5 > 5;

Query result is 0, which means FALSE

Let's look at the same example using NULL

SELECT NULL > NULL;

Allows the use of the IS NULL keyword

SELECT 5 IS NULL;

Query result is 0 which is FALSE

SELECT NULL IS NULL;

Query result - 1, TRUE

Summary

• NULL is a value place holder for optional table fields.
• MySQL treats the NULL value differently from other data types. The NULL values ​​when used in a condition evaluates to the false Boolean value.
• The NOT logical operate is used to test for Boolean values ​​and evaluates to true if the Boolean value is false and false if the Boolean value is true.
• The NOT NULL clause is used to eliminate NULL values ​​from a result set
• Performing arithmetic operations on NULL values ​​always returns NULL results.
• The comparison operators such as [, =, etc.] cannot be used to compare NULL values.