Lesson: Understanding NULL Values
130
Lesson Overview
This lesson explains what NULL values are and \vhy dealing with them correctly is important.
Lesson Objectives
After completing this lesson, you will be able to:
• Explain ho\\1 to interpret NULL values in databases.
• Understand ·why the presences ofN-ULL values can lead to unexpected query
results.
• Analyze if two seemingly equivalent SQL statements return different results
because of NULL values.
Business Example
What is a NULL value?
Figure 269: NULL Values
A NULL value signals that the corresponding value is unkno\vn or does not exist. The NULL value is a special value reserved by the DBMS.
• For numeric data types the NULL value is not equal to 0.
• For string based data types the NULL value is not equal to an empty string or
to the space character. A NULL value is used, ...
• If the corresponding value exists in principle, but is (temporarily or permanently) unknown (such as a birthday of a person)
• If the corresponding value does not exist (for example the owner of an unregistered car).
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Understanding NULL Values
How are NULL values created?
Figure 270: NULL Values
NULL values are "created" \vhen reading rows (SELECT statement)
• If the source table already contains NULL values
• When using outer joins
NULL values are created in a table when inserting rows (INSERT statement) if
• No value for a collunn is provided, NULL values are allowed and no default
value is defined
• A NULL value is explicitly specified for a column and NULL values are allo\ved
NULL values are created in a table when changing rows (UPDATE statement) if • A NULL value is explicitly specified for a column and NULL values are
allo\ved
NULL values are created in a table when adding a column (ALTER TABLE statement), if
• The table has at least one row, NULL values arc allowed for the added
column and no default value is defined for the added column
NULL values enforce a trivalent logic
Unit 6: Understanding NULL Values HA150
132
• By allowing NULL values a third truth value is necessary.
• In addition to "true" and "false" there is also the truth value "unknown"
in SQL.
• Any comparison with a NULL value results in the truth value "unknown".
• This applies to all comparison operators: =, >, >=, <, <=, <>
A A=7
false
true
7
NULL unknown
Figure 272: NULL Values
true
false unknown Figure 273: Trivalent Logic: NOT
true true true false true unknown false true false false false unknown unknown true unknown false unknown unknown
Figure 274: Trivalent Logic: AND
A<>7 true false unknown NOTX false true unknown X ANDY true false unknown false false false unknown false unknown
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
A>=6
false true unknown
HA150 Lesson: Understanding NULL Values
XOR Y
true true true
true false true
true unknown true
false true true
false false false
false unknown unknown
unknown true true
unknown false unknown
unknown unknown unknown
Figure 275: Trivalent Logic: OR
The trivalent logic has only one more truth value, so where is the
problem? Figure 276: Trivalent Logic
39 = 19,683 binary logical operators are conceivable
X op Y
true true true I false I unknown
true false true I false I unknown
true unknown true I false I unknown
false true true I false I unknown
false false true I false I unknown
false unknown true I false I unknown
unknown true true I false I unknown
unknown false true I false I unknown
Unit 6: Understanding NULL Values HA150
134
Many "obviously true" statements are no longer necessarily true!!! +A=A
+(A < 5) OR ( A >= 5)
+(A = 'Walldorf') OR (A <> 'Walldorf) +A+ 1 >A
+A• A>= 0
+0 •A= 0 +2 • A = A+ A
+MAXIMUM >=ALL (set of all values) +MINIMUM <=ALL (set of all values) +A OR (NOT A)
Figure 278: NULLs & Trivalent Logic
• Do the following two SQL queries have the same result?
SELECT Name, overtime
FROM Official
WHERE overtime <- 10 OR overtime > 10;
• • • • • • . • • •.• • • • • .·.· .•.•• •• • .·.•.•• • • • •.•.• .•: • • • • •.• •.• • • • • • •• • .• • • • • • • • • • • •• • •.• •• • • • • • •• • .• • • • •: • • • • • .• • . • : .-,• .• .•.• • • • • •:.•.• •,• • .•• • • • • • • •••.•.• .•:• • • •.•• • • •• • .•.• "N ..• • • •• • •• • • • • • • •.• • •:.••: •• •• • • • :• • • • • • • • • • • • ... •.•:: •• ••
SELECT Name, overtime
FROM Official;
Figure 279: NULL Values
• The two SQL queries have different results!
;... Only officials where Overtime IS NOT NULL applies:
SELECT Name, overtime FROM Official
WHERE overtime < 10 OR overtime > 10;
;... All officials (including those with: Overtime SELECT Name, overtime
FROM Official;
Figure 280: NULL Values
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
IS NULL) NAME OVERTIME --- Mr A 10 Mr B 10 Ms C 20 Mr E 10 Mr F 18 Ms G 22 OVERTIME: --- Mr A 10 Mr B 10 Ms C 20 Ms D ? Mr E 10 Mr F 18 Ids G 22 ?ifs H ? Mr I ? 2014
• Do the following two SQL queries have the same result?
SELECT *
FROM Official
WHERE PNr <> ALL (SELECT PNr
FROM Official
WHERE overtime = overtime) ;
SELECT *
FROM Official
WHERE overtime Is NULL;
Figure 281: NULL Values
• The two SQL queries have the same result!
SELECT *
FROM Official
WHERE PNr <> ALL (SELECT PNr
FROM Official
WHERE overtime = Overtime) ;
PNR NMIE OVERl'DIE SJILAll1' WH?GER
--·----·-- ---
SELECT * POt IU D ? All
FROM Official P08 "" H ? ""
WHERE Overtime IS NULL; P09 ICr I ? AH
Figure 282: NULL Values
> The sub query returns all personnel numbers of the officials, where the number of overtime hours is known.
SELECT *
FROM Official
WHERE PNr <> ALL (SELECT PNr
FROM Official
WHERE overtime = Overtime);
Figure 283: NULL Values
--- P09 P09 ? PNR POl P02 P03 POS P06 P07
Unit 6: Understanding NULL Values HA150
136
;.. The outer SELECT query returns all officials, whose PersNumber differs from all PersNumbers of the sub query.
,.. So the outer SELECT query returns all officials, whose PersNumber differs from the PersNumbers of those officials whose overtime is known.
,.. So the outer SELECT query returns all officials whose overtime is unknown.
SELECT *
FROM Officia1
WHERE PNr <>ALL (SELECT PNr
FROM Officia1
WHERE Overtime - Overtime);
PNR NAME OVERTIME SAIJIRY
--- ---
P04 Ms D ? A12
P08 Ms H ? A13
P09 Mr I ? A14
Figure 284: NULL Values
• Do the following two SQL queries have the same result?
SELECT Name, Overtime FROM Officia1
WHERE overtime >= ALL (SELECT overtime FROM Officia1) ;
SELECT Name, Overtime FROM Of ficia1
WHERE overtime = (SELECT MAX (overtime)
FROM Officia1) ;
Figure 285: NULL Values
• The two SQL queries have different results!
1>11\NAGER
---
P09
1?09 ?
SELECT Name, Overtime The query resuit J.s empty.
FROM Officia1
WHERE overtime >= ALL (SELECT overtime
FROM Officia1) ;
SELECT Name, Overtime FROM Off icia1
WHERE overtime = (SELECT MAX (overtime)
FROM Officia1) i
Figure 286: NULL Values
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
Ms G 22
HA150 Lesson: Understanding NULL Values
+ The sub query also returns NULL values.
SELECT Name, Overtime
FROM Official
WHERE Overtime >= ALL (SELECT Overtime
FROM Official) 10 10 20 ? 10 18 22 ? ?
Hence there is no value which is greater or equal to all sub query values!
Tile query :result is anpty.
SELECT Name, Overtime
FROM Official
WHERE Overtime >= ALL (SELECT OVertiUIA
FROM OfficJ.. tl) ;
Figure 287: NULL Values
• Do the following two SQL queries have the same result?
SELECT Name, Overtime FROM Official
WHERE Overtime >= ALL (SELECT Overtime FROM Official.
SELECT Name f Overtime
FROM Official.
WHERE Overtime IS NOT NULL) ;
WHERE Overtime = (SELECT MAX (Overtime) FROM Official) ;
Figure 288: NULL Values
+ For the existing data, both queries return the same result. SELECT Name, Overtime
FROM Official
NAME OVERTIME
WHERE Overtime >= ALL (SELECT Overtime
FROM Official.
Ma G 22
WHERE Overtime IS NOT NULL) ;
SELECT Name, Overtime FROM Official
WHERE Overtime = (SELECT MAX (Overtime)
FROM Official) ;
Figure 289: NULL Values
NAME OVERTIME
Unit 6: Understanding NULL Values HA150
138
+ But: If the overtime values were unknown for all officials , would the two SQL queries still return the same results?
Is tbe query .result eq;.ty
Or
SELECT Name, overtime FROM Official
WHERE overtime >= ALL (SELECT
FROM
Does it contain all rows 0£ tha official tabla?
overtime Official
WHERE overtime IS NOT NULL) ;
SELECT Name, overtime
FROM Official
The query result is empty.
WHERE overtime = (SELECT MAX (overtime)
FROM Official) ;
Figure 290: NULL Values
+ The result of the sub query is empty.
+ Is the WHERE clause of the outer query fulfilled for all rows, or false?
In SAP HANll.:
SELECT Name, Overtime
FROM Official The query resul.t is empty.
WHERE Overtime >= ALL (SELECT Overtime
FROM Official
WHERE Overtime IS NOT NULL) ;
+ The sub query returns a NULL value.
+ So the WHERE clause of the outer query is not fulfilled for any row.
SELECT Name, Overtime FROM Official
The query result is empty.
WHERE Overtime = (SELECT MAX(Overtime)
FROM Official); Figure 291: NULL Values
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Understanding NULL Values
Lesson Summary You should now be able to:
• Explain ho\V to interpret NULL values in databases.
• Understand why the presences of NULL values can lead to unexpected query results.
• Analyze if two seemingly equivalent SQL statements return different results
Unit Summary
140
HA150
Unit Summary
You should now be able to:
•
•
•
Explain how to interpret NULL values in databases .
Understand \vby the presences of NULL values can lead to unexpected query results.
Analyze if two seemingly equivalent SQL statements return different results because of NULL values.
© 2014 SAP AG or an SAP affiliate company.
n1
Changing Data Stored in Tables
Unit Overview
Unit Objectives
After completing this unit, you will be able to:
• Add rows to database tables using SQL. • Change existing rows of a database table.
• Remove existing rO\VS from a database table.
Unit Contents
Lesson: Changing Data Stored in Tables . . . ... . . 142
�
Unit 7: Changing Data Stored in Tables HA150
Lesson: Changing Data Stored in Tables Lesson Overview
This lesson covers how you can add data to a database, change and delete data.
142
Lesson Objectives
After completing thjs lesson, you wi II be able to:
• Add rows to database tables using SQL.
• Change existing rows of a database table. • Remove existing rows from a database table. Business Example
Which SQL statements can be used to insert, change or delete
data?
Figure 292: Changing Database Access INSERT statement
• New rows are inserted into a
table
UPDATE statement
• Existing table rows are
changed
DELETE statement
• Existing table rows are deleted
UPSERT statement
• New rows are inserted into a
table,
existing table rows are changed
REPLACE statement
• Same as UPSERT
Figure 293: Changing Database Access
INSERT statement Figure 294: Changing Database Access
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data Stored in Tables
INSERT INTO Table VALUES (Value, Value, Value) i
INSERT INTO Table(Column, Column) VALUES (Value,
Value) ;
INSERT INTO Table SELECT ... FROM ... WHERE ... ,
Figure 295: INSERT
Using INSERT you can specify a value for each column.
+If the sequence of the values corresponds to the sequence of columns, you do not need to list the column names.
+The order of the columns above mentioned is set Implicitly when you create the table.
:.Insert a new row into the Official table:
INSERT INTO Official
VALUES ('Pl0', 'Ms J', 20, 'AlO',
PNR NAME OVERTIME SALARY MANAGER
PlO Ms J 20
Figure 296: INSERT
A10 P04
Using INSERT you can specify a value for each column.
I P04 I ) i
+If the sequence of the values does not match the sequence of the corresponding columns, you must list the column names in the correct sequence of the values.
·1-1nsert a new row into the Official table:
INSERT INTO Official ( Salary, Overtime, Name, PNr, Manager)
VALUES ( 1 A12 1 , so, I Mr K' , 'Pll' , 1 P09 I) ;
PNR NAME OVERTIME SALARY MANAGER
Pll Mr K 50
Figure 297: INSERT