Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using Operators with NULL

If a table has NULL values in it, a special pair of comparison operators that can be used to include or omit NULL valued fields. You can check for fields set to NULL using the IS NULL keyword phrase. In order to check for a non-NULL value, use the IS NOT NULL keyword phrase.

Example 5-15 uses the IS NULL keyword to check for authors whose first_name column value are set to NULL.

Example 5-15. Comparisons using IS NULL

booktown=# 
SELECT last_name, first_name

booktown-# 
       FROM authors

booktown-# 
       WHERE first_name IS NULL;

 last_name | first_name
-----------+------------
 Geisel    |
(1 row)

Examining Example 5-15 and Example 5-16, you might think that the syntax in the two statements provided are identical. There is, however, a key difference.

Example 5-16. Comparisons equal to NULL

booktown=# 
SELECT last_name, first_name

booktown-# 
       FROM authors

booktown-# 
       WHERE first_name = NULL;

 last_name | first_name
-----------+------------
 Geisel    |
(1 row)

PostgreSQL provides a translation from = NULL to IS NULL, and likewise for the != NULL operation with IS NOT NULL. This is provided only for compatibility with existing client applications (such as Microsoft Access).

When comparing a value to NULL in an expression, be in the habit of using the IS NULL and IS NOT NULL keyword operators rather than the = or != math-style operators. While this translation is provided for the sake of compatibility with other systems, it may be discontinued in the future, as it is not a standard SQL procedure (and it is certainly not guaranteed to be a portable procedure to other SQL-based database systems for the same reason).

Any as-yet undiscussed comparison operator used on a NULL value will return a NULL value, as NULL will never be larger, smaller, or otherwise related to any non-NULL value. (See Example 5-17.) A direct query on the result of a comparison against a NULL value will therefore return NULL. You can think of a NULL value as being a sort of SQL black hole, from which no comparison (outside of IS NULL, and its special = translation) may return true, and to which no values may be added, or concatenated.

Example 5-17. Using operators with NULL values

booktown=# 
\pset null *null*
 Null display is '*null*'.
booktown=# 
SELECT 5 > NULL;

 ?column?
----------
 *null*
(1 row)

booktown=# 
SELECT NULL IS NULL;

 ?column?
----------
 t
(1 row)

booktown=# 
SELECT NULL || 'Test';

 ?column?
----------
 *null*
(1 row)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire