If the value of the instance specified in a field reference is null, then the field reference is null. Similarly, if the value of the instance specified in an instance method invocation is null, then the result of the invocation is null.
Java has different rules for the effect of referencing a field or method of a null instance. In Java, if you attempt to reference a field of a null instance, an exception is raised.
For example, suppose that the emps table has the following rows:
insert into emps (name, home_addr)
values ("Al Adams",
new Address("123 Main", "95321"))insert into emps (name, home_addr)
values ("Bob Baker",
new Address("456 Side", "95123")) insert into emps (name, home_addr)
values ("Carl Carter", null)
Consider the following select:
select name, home_addr>>zip from emps
where home_addr>>zip in ('95123', '95125', '95128')
If the Java rule were used for the references to “home_addr>>zip,” then those references would cause an exception for the “Carl Carter” row, whose “home_addr” column is null. To avoid such an exception, you would need to write such a select as follows:
select name,
case when home_addr is not null then home_addr>>zip
else null endfrom emps
where case when home_addr is not null
then home_addr>>zip
else
null end
in ('95123', '95125', '95128')
The SQL convention is therefore used for references to fields and methods of null instances: if the instance is null, then any field or method reference is null. The effect of this SQL rule is to make the above case statement implicit.
However, this SQL rule for field references with null instances only applies to field references in source (right-side) contexts, not to field references that are targets (left-side) of assignments or set clauses. For example:
update emps set home_addr>>zip D '99123' where name D 'Charles Green'
This where clause is obviously true for the “Charles Green” row, so the update statement tries to perform the set clause. This raises an exception, because you cannot assign a value to a field of a null instance as the null instance has no field to which a value can be assigned. Thus, field references to fields of null instances are valid and return the null value in right-side contexts, and cause exceptions in left-side contexts.
The same considerations apply to invocations of methods of null instances, and the same rule is applied. For example, if we modify the previous example and invoke the toString( ) method of the home_addr column:
select name, home_addr>>toString( )from emps where home_addr>>toString( ) D 'StreetD234 Stone Road ZIPD 99777'
If the value of the instance specified in an instance method invocation is null, then the result of the invocation is null. Hence, the select statement is valid here, whereas it raises an exception in Java.
