Null Values




Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations.

The result of an arithmetic expression (involving, for example +, −, ∗, or /) is
null if any of the input values is null. For example, if a query has an expression
r.A+ 5, and r.A is null for a particular tuple, then the expression result must also
be null for that tuple.

Comparisons involving nulls are more of a problem. For example, consider
the comparison “1 < null”. It would be wrong to say this is true since we do not
know what the null value represents. But it would likewise be wrong to claim this
expression is false; if we did, “not (1 < null)” would evaluate to true, which does
not make sense. SQL therefore treats as unknown the result of any comparison
involving a null value (other than predicates is null and is not null, which are
described later in this section). This creates a third logical value in addition to true
and false.

Since the predicate in a where clause can involve Boolean operations such
as and, or, and not on the results of comparisons, the definitions of the Boolean
operations are extended to deal with the value unknown.

Topics You May Be Interested In
Specialization And Generalization Xml: Extensible Markup Language
Constraints And Characteristics Of Specialization And Generalization Hierarchies Schema Diagrams
View Of Data Example Of Notation- Uml Class Diagrams
Relational Databases Sensitive Data And Types Of Disclosures
Discretionary Access Control Based On Granting And Revoking Privileges Aggregate Functions

and: The result of true and unknown is unknown, false and unknown is false,
while unknown and unknown is unknown.

or: The result of true or unknown is true, false or unknown is unknown, while
unknown or unknown is unknown.

not: The result of not unknown is unknown.

You can verify that if r.A is null, then “1 < r.A” as well as “not (1 < r.A)”
evaluate to unknown.

Topics You May Be Interested In
Purpose Of Database Systems Basic Structure Of Sql Queries
Database Architecture Entity Types, Entity Sets, Keys, And Value Sets-2
Relational Operations Refining The Er Design For The Company Database
Overview Of The Sql Query Language Relationship Types Of Degree Higher Than Two
Sql Data Definition Ordering The Display Of Tuples

If the where clause predicate evaluates to either false or unknown for a tuple,
that tuple is not added to the result.

SQL uses the special keyword null in a predicate to test for a null value. Thus,
to find all instructors who appear in the instructor relation with null values for
salary, we write:

select name
from instructor
where salary is null;

The predicate is not null succeeds if the value on which it is applied is not null.

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design The Natural Join
Database Architecture Additional Basic Operations-string Operations
History Of Database Systems Entity Types, Entity Sets, Keys, And Value Sets-2
Structure Of Relational Databases Initial Conceptual Design Of The Company Database
Relational Operations Attribute Specification In Select Clause

Some implementations of SQL also allow us to test whether the result of a com?parison is unknown, rather than true or false, by using the clauses is unknown
and is not unknown.

When a query uses the select distinct clause, duplicate tuples must be eliminated. For this purpose, when comparing values of corresponding attributes
from two tuples, the values are treated as identical if either both are non-null and
equal in value, or both are null. Thus two copies of a tuple, such as {(’A’,null),
(’A’,null)}, are treated as being identical, even if some of the attributes have a
null value. Using the distinct clause then retains only one copy of such identical
tuples. Note that the treatment of null above is different from the way nulls are
treated in predicates, where a comparison “null=null” would return unknown,
rather than true.

The above approach of treating tuples as identical if they have the same
values for all attributes, even if some of the values are null, is also used for the
set operations union, intersection and except.



Frequently Asked Questions

+
Ans: The SQL operations union, intersect, and except operate on relations. view more..
+
Ans: Four main control measures are used to provide security of data in databases: Access control, Inference control, Flow control, Data encryption view more..
+
Ans: SQL includes a between comparison operator to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value. view more..
+
Ans: Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations. view more..
+
Ans: Aggregate functions view more..
+
Ans: The main Idea for the Sampling Algorithm is to select a small sample , one that fits in main memory of the database of transaction view more..
+
Ans: The main idea of Sampling Algorithm is to select a small sample , one that fits in main memory , of the database of transaction view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, and the ability to include in the result tuples that are excluded by natural join. We shall discuss these forms of join in this section. view more..
+
Ans: In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 4/5
540 views

Advertisements