*Null Values and Comparisons Involving NULL*

SQL allows attributes to have a special value NULL, which is called the null value. There are many different interpretations that can be put on null values. Here are some of the most common:

1. Value unknown: that is, "I know there is some value that belongs here but I don’t know what it is." An unknown birthdate is an example.

2. Value inapplicable: "There is no value that makes sense here." For instance, if we had a spouse attribute for the MovieStar relation, then an unmarried star might have NULL for that attribute, not because we don’t know the spouse's name, but because there is none.

3. Value withheld: "We are not entitled to know the value that belongs here." For instance, an unlisted phone number might appear as NULL in the component for a phone attribute.

We saw in "Outerjoins" how the use of an outerjoin operator produces null values in some components of tuples; SQL allows outerjoins and also produces NULL’s when a query involves outerjoins; see "Outerjoins". There are other ways SQL produces NULL’s as well. For instance, certain insertions of tuples create null values, as we shall see in "Database Modifications".

In WHERE clauses, we must be prepared for the possibility that a component of some tuple we are examining will be NULL. There are two important rules to remember when we operate upon a NULL value.

1. When we operate on a NULL and any value, including another NULL, using an arithmetic operator like x or +, the result is NULL.

2. When we compare a NULL value and any value, including another NULL, using a comparison operator like = or >, the result is UNKNOWN. The value UNKNOWN is another truth-value, like TRUE and FALSE; we shall discuss how to manipulate truth-value UNKNOWN shortly.

However, we must remember that, although NULL is a value that can appear in tuples, it is not a constant. Thus, while the above rules apply when we try to operator on an expression whose value is NULL, we cannot use NULL explicitly as an operand.**Example 1:** Let x have the value NULL. Then the value of x + 3 is also NULL. However, NULL + 3 is not a legal SQL expression. Similarly, the value of x = 3 is UNKNOWN, because we cannot tell if the value of x, which is NULL, equals the value 3. However, the comparison NULL = 3 is not correct SQL.

Incidentally; the correct way to ask if x has the value NULL is with the expression x IS NULL. This expression has the value TRUE if x has the value NULL and it has value FALSE otherwise. Similarly, x IS NOT NULL has the value TRUE unless the value of x is NULL.

### Tags

- sql
- null values
- tuples
- attributes
- Queries in PSM
- Scrolling Cursors
- Protecting Against Concurrent Updates
- Modifications by Cursor
- Cursors
- Instead-Of Triggers
- Triggers in SQL
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Tuple Variables
- Disambiguating Attributes
- Queries Involving More Than One Relation
- The Truth-Value UNKNOWN
- Dates and Times
- Comparison of Strings
- Selection in SQL
- Projection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Extending the Projection Operator
- Grouping
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Information Integration Via Semistructured Data
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- The Object-Relational Model
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Other Data Models
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Decomposing Relations
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- From E/R Diagrams to Relational Designs
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Converting Multiway Relationships to Binary
- Attributes on Relationships
- Multiway Relationships
- Elements of the E/R Model
- Database System Implementation
- Database Programming
- Database Design
- Multimedia Data
- Relational Database Systems