SQL Null Values

Last modified on: December 2, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="4300c005-1296-4c20-aaf1-32d7fe704917" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3><strong>SQL NULL Values: A Comprehensive Guide</strong></h3>
<h4><strong>What is a NULL Value in SQL?</strong></h4>
<p>In SQL, <strong><code>NULL</code></strong> represents a missing or undefined value in a table. It is not the same as zero, an empty string (<code>""</code>), or a blank space&mdash;it signifies the absence of a value.</p>
<hr />
<h3><strong>Key Features of NULL Values</strong></h3>
<ol>
<li><strong>No Value</strong>: It indicates that a value is unknown or not applicable.</li>
<li><strong>Non-Comparable</strong>: <code>NULL</code> cannot be compared directly using equality (<code>=</code>) or inequality (<code>!=</code>) operators.</li>
<li><strong>Requires Special Handling</strong>: Use the <strong><code>IS NULL</code></strong> or <strong><code>IS NOT NULL</code></strong> operators to test for <code>NULL</code>.</li>
</ol>
<hr />
<h3><strong>How to Work with NULL Values</strong></h3>
<h4><strong>1. Checking for NULL</strong></h4>
<p>Use the <strong><code>IS NULL</code></strong> operator to find rows with <code>NULL</code> values:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary 
FROM employees 
WHERE salary IS NULL;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>2. Excluding NULL</strong></h4>
<p>Use the <strong><code>IS NOT NULL</code></strong> operator to exclude rows with <code>NULL</code> values:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary 
FROM employees 
WHERE salary IS NOT NULL;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Examples of NULL in SQL</strong></h3>
<h4><strong>1. Finding Records with NULL</strong></h4>
<p>Assume we have a table named <code>employees</code>:</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>position</th>
<th>salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Doe</td>
<td>Manager</td>
<td>75000</td>
</tr>
<tr>
<td>2</td>
<td>Jane Smith</td>
<td>Analyst</td>
<td>NULL</td>
</tr>
<tr>
<td>3</td>
<td>Alice Brown</td>
<td>Developer</td>
<td>55000</td>
</tr>
</tbody>
</table>
<p>Query to find employees with no salary specified:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name 
FROM employees 
WHERE salary IS NULL;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output:</strong></p>
<table>
<thead>
<tr>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Jane Smith</td>
</tr>
</tbody>
</table>
<hr />
<h4><strong>2. Handling NULL in Calculations</strong></h4>
<p>When performing arithmetic operations, <code>NULL</code> values result in <code>NULL</code>. To replace <code>NULL</code> with a default value, use <strong><code>COALESCE</code></strong>:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary, COALESCE(salary, 0) AS adjusted_salary 
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output:</strong></p>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>adjusted_salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>John Doe</td>
<td>75000</td>
<td>75000</td>
</tr>
<tr>
<td>Jane Smith</td>
<td>NULL</td>
<td>0</td>
</tr>
<tr>
<td>Alice Brown</td>
<td>55000</td>
<td>55000</td>
</tr>
</tbody>
</table>
<hr />
<h4><strong>3. NULL in Conditions</strong></h4>
<p>Retrieve employees whose salary is either <code>NULL</code> or greater than $60,000:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary 
FROM employees 
WHERE salary IS NULL OR salary &gt; 60000;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>4. Sorting with NULL Values</strong></h4>
<p>By default, most SQL databases place <code>NULL</code> values at the top when sorting in ascending order:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary 
FROM employees 
ORDER BY salary ASC;
</code></pre>
</div>
</div>
</div>
</div>
<p>To place <code>NULL</code> values at the end, use <code>NULLS LAST</code> (if supported by your database):</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, salary 
FROM employees 
ORDER BY salary ASC NULLS LAST;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Functions for Handling NULL Values</strong></h3>
<h4><strong>1. COALESCE</strong></h4>
<p>Returns the first non-<code>NULL</code> value from a list of arguments:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, COALESCE(salary, 50000) AS salary 
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>2. IFNULL (MySQL-specific)</strong></h4>
<p>Replaces <code>NULL</code> with a specified value:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT name, IFNULL(salary, 50000) AS salary 
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>3. NULLIF</strong></h4>
<p>Returns <code>NULL</code> if two expressions are equal:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT NULLIF(salary, 0) AS result 
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Tips for Handling NULL</strong></h3>
<ol>
<li><strong>Avoid Direct Comparisons</strong>: Use <code>IS NULL</code> and <code>IS NOT NULL</code> instead of <code>=</code> or <code>!=</code>.</li>
<li><strong>Use Default Values</strong>: Replace <code>NULL</code> with meaningful defaults using <code>COALESCE</code> or <code>IFNULL</code>.</li>
<li><strong>Test for NULL</strong>: Always account for <code>NULL</code> when writing queries involving conditional logic.</li>
</ol>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More

SQL Null Values

Learn Ansible

Learn HTML

Learn Angular 3

Learn PHP

Learn Angular 2.0

Learn Python

Learn C# Sharp

Learn CSS

Learn Node Js

Learn JQuery Framework

Learn SQL Tutorial

Learn MySql

Learn PHP 7.1

Learn Angular 3

Learn Angular 2

Learn HTML5

Learn Angular 2

Learn Angular 3.0

Learn Bootstrap 3

Learn Bootstrap 4

Learn Bootstrap 5

Learn JQuery Tutorials