您在這裡

Nulls

20 十月, 2015 - 10:38

This is a special symbol, independent of data type, which means either unknown or inapplicable. (it does not mean zero or blank)

  • No data entry
  • Not permitted in primary key
  • Should be avoided in other attributes
  • Can represent
  • An unknown attribute value
  • A known, but missing, attribute value
  • A “not applicable” condition
  • Can create problems when functions such as COUNT, AVERAGE, and SUM are used
  • Can create logical problems when relational tables are linked

NOTE: the result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions which ignore nulls)

media/image6.png

PROBLEM: Find all employees (EMP#) in Sales whose salary plus commission is greater than 30,000.

SELECT emp# FROM salary_tbl

WHERE jobname = ‘Sales’ AND

(commission + salary) > 30000 –> E10 and E12

This result does not include E13 because of the Null value in Commission. To ensure the row with the null value is included, we need to look at the individual fields. By adding commission and Salary for employee E13, the result will be a null value. The solution is shown below.

SELECT emp# FROM salary_tbl

WHERE jobName = ‘Sales’ AND

(commission > 30000 OR salary > 30000 OR

(commission + salary) > 30000 ) –>E10 and E12 and E13