Home arrow Oracle arrow Page 2 - Retrieving Table Data with the LIKE Operator

How to use the IS NULL condition - Oracle

In this eighth part of a nine-part article series focusing on the SELECT statement and its usage in retrieving data from tables, you'll learn how to use the LIKE operator and much more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

TABLE OF CONTENTS:
  1. Retrieving Table Data with the LIKE Operator
  2. How to use the IS NULL condition
  3. How to code the ORDER BY clause
By: Murach Publishing
Rating: starstarstarstarstar / 0
August 31, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In chapter 1, you learned that a column can contain a null value. A null isnít the same as zero, a blank string that contains one or more spaces (Ď í), or an empty string that doesnít contain any spaces (Ďí). Instead, a null value indicates that the data is not applicable, not available, or unknown. When you allow null values in one or more columns, you need to know how to test for them in search conditions. To do that, you can use the IS NULL condition, as shown in figure 3-16.

This figure uses a table named null_sample to illustrate how to search for null values. This table contains two columns. The first column, invoice_id, is an identification column. The second column, invoice_total, contains the total for the invoice, which can be a null value. As you can see in the first example, the invoice with an invoice_id of 3 contains a null value.

The second example in this figure shows what happens when you retrieve all the invoices with invoice totals equal to zero. Notice that the row with a null invoice total isnít included in the result set. Likewise, it isnít included in the result set that contains all the invoices with invoices totals that arenít equal to zero, as illustrated by the third example. Instead, you have to use the IS NULL condition to retrieve rows with null values, as shown in the fourth example.

You can also use the NOT operator with the IS NULL condition as illustrated in the last example in this figure. When you use this operator, all of the rows that donít contain null values are included in the query results.

The syntax of the WHERE clause with the IS NULL condition

WHERE expression IS [NOT] NULL

The contents of the Null_Sample table

SELECT *
FROM null_sample

A SELECT statement that retrieves rows with zero values

SELECT *
FROM null_sample
WHERE invoice_total = 0

A SELECT statement that retrieves rows with non-zero values

SELECT *
FROM null_sample
WHERE invoice_total <> 0

A SELECT statement that retrieves rows with null values

SELECT *
FROM null_sample
WHERE invoice_total IS NULL

A SELECT statement that retrieves rows without null values

SELECT *
FROM null_sample
WHERE invoice_total IS NOT NULL

--------------------------------------------Figure 3-16 How to use the IS NULL condition



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: