MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

IS NOT NULL Operator
A NULL value in a MySQL table indicates a missing or unknown value. It appears to be blank and does not contain any data. This is different from zero values.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. To check for NULL values in a table column, we can use two basic operators:
IS NULL
IS NOT NULL
MySQL IS NOT NULL Operator
The MySQL IS NOT NULL operator is used to verify whether a particular column has a non-null value or not. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.
By using the IS NOT NULL operator in a conditional clause, we can only fetch the records that contain valid data in a particular column.
Syntax
Following is the syntax of IS NOT NULL in MySQL 
SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IS NOT NULL;
Example
Firstly, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 
In the following query, we are using the INSERT statement to insert values to the table 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', NULL),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', NULL, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', NULL),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', NULL, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
The table is created as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
NULL
2
Khilan
25
Delhi
1500.00
3
Kaushik
NULL
Kota
2000.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Bhopal
8500.00
6
Komal
NULL
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Example
In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is not NULL.
SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;
Output
Following output is produced 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
NULL
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
IS NOT NULL with COUNT() function
We can use the IS NOT NULL operator along with the MySQL COUNT() function to count only the non-null values in a specific column(s).
Syntax
Following is the syntax of the IS NOT NULL with COUNT() function in MySQL 
SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NOT NULL;
Example
The following query returns the count of all rows in the CUSTOMERS table where the ADDRESS column is not NULL.
SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;
Output
On executing the above query, it will generate an output as shown below 
COUNT(*)
5
IS NOT NULL with UPDATE statement
In MySQL, we can update all the non-null rows in a specific column(s) using the UPDATE statement with IS NOT NULL operator.
Syntax
Following is the syntax of the IS NOT NULL operator with the UPDATE statement in MySQL 
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;
Example
In the following query, we will update the SALARY column to a value of 9000 for all records where the SALARY column is not NULL 
UPDATE CUSTOMERS
SET SALARY = 20000
WHERE SALARY IS NOT NULL;
Verification
To check whether the table has been updated or not, execute the SELECT query to display the CUSTOMERS table.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
NULL
2
Khilan
25
Delhi
20000.00
3
Kaushik
NULL
Kota
20000.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Bhopal
20000.00
6
Komal
NULL
Hyderabad
20000.00
7
Muffy
24
Indore
20000.00
IS NOT NULL with DELETE statement
In MySQL, we can delete all the non-null rows in a specific column(s) using the DELETE statement with IS NOT NULL operator.
Syntax
Following is the syntax of the IS NOT NULL operator with the DELETE statement in MySQL 
DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;
Example
In the following query, we are trying to delete records which are not null in the AGE column of CUSTOMERS table.
DELETE FROM CUSTOMERS
WHERE AGE IS NOT NULL;
Verification
To verify whether the table has been updated or not, display the table using a SELECT query.
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
NULL
Kota
20000.00
6
Komal
NULL
Hyderabad
20000.00
IS NOT NULL Operator Using Client Program
In addition to executing the IS NOT NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.
Syntax
Following are the syntaxes of the IS NOT NULL Operator in MySQL table in various programming languages 
PHPNodeJSJavaPython
To execute the IS NOT NULL Operator in MySQL through a PHP program, we need to execute the SQL query with IS NOT NULL operator using the mysqli function named query() as 
$sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NOT NULL";
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM CUSTOMERS  WHERE AGE IS NOT NULL";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",   
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Table records:
Id 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 0.000000
Id 2, Name: Khilan, Age: 25, Address , Salary 1500.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 0.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.000000
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

IS NOT NULL Operator

MySQL IS NOT NULL Operator

Syntax

Example

Example

Output

IS NOT NULL with COUNT() function

Syntax

Example

Output

IS NOT NULL with UPDATE statement

Syntax

Example

Verification

IS NOT NULL with DELETE statement

Syntax

Example

Verification

IS NOT NULL Operator Using Client Program

Syntax

Example

Output