Syntax SQL NULL Values.
1.IS NULL
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
2.IS NOT NULL
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Lets Learn.
1.Create database and table.
Student table.
StudentID | StudentName | Address | Country | CourseID | DOB |
---|---|---|---|---|---|
1 | Liam | 23 Andrew road | USA | 5 | 1995-08-21 |
2 | Andrew | 2415 Anthony market str | Germany | 6 | NULL |
4 | Ryan | 18 Christopher sq. | INDIA | 2 | NULL |
5 | Charles | 878 Eli Berguvsvägen 8 | UK | 1 | 1995-01-11 |
8 | Anikth | 23 Andrew road | AFRICA | 3 | NULL |
9 | Andrew | 2415 Anthony market str | Germany | 3 | 1996-04-18 |
10 | Bhavya | 18 Christopher sq. | INDIA | 3 | NULL |
for this above student table we need to check which fields are NULL and which fields are NOT NULL by SQL queries .
Example(1)
SELECT StudentName,Address,DOB
FROM student
WHERE DOB IS NULL;
above SQL Statement selecting student name ,address and dob from student table where DOB is NULL
Example(2)
SELECT StudentName,Address,DOB
FROM student
WHERE DOB IS NOT NULL
above SQL Statement selecting student name ,address and dob from student table where DOB is NOT NULL
2.Complete code of SQL NULL Values. in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to use SQL NULL Values.</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="text-center">
<h1>SQL NULL Values</h1>
</div>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql_1 = "SELECT StudentName,Address,DOB FROM student WHERE DOB IS NULL;";
$sql_2 = "SELECT StudentName,Address,DOB FROM student WHERE DOB IS NOT NULL;";
echo "<h4 class='text-danger'>Example(1)</h4>";
if($result_1 = mysqli_query($connect, $sql_1)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>".$row['StudentName'] /*student table*/ ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo "<h4 class='text-danger'>Example(2) </h4>";
if($result_1 = mysqli_query($connect, $sql_2)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>".$row['StudentName'] /*student table*/ ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
</div>
<br>
</div>
</body>
</html>