22_NULL.pdf - CITS1402 Relational Database Management...

This preview shows page 1 - 10 out of 17 pages.

CITS1402 Relational Database Management Systems Video 22 — NULL Gordon Royle Department of Mathematics & Statistics
The special value NULL Real-life databases are often incomplete, but still must be queried. To deal with this, SQL has a special value NULL that means either I The data is absent or unknown , or I Data in this field would have no meaning . The value NULL and the behaviour of SQL operators and functions allows the user to deal with this sensibly.
A synthetic table
Operators If part of an arithmetic expression is NULL then so is the final value
Comparisons Any normal comparison involving NULL has the value NULL , so neither of these queries produce any output. SELECT * FROM Scores WHERE A == NULL ; SELECT * FROM Scores WHERE A != NULL ; So no value is equal to NULL or not equal to NULL .
Checking for NULL We can check to see if a “value” in the field is actually NULL SELECT * FROM Scores WHERE A IS NULL ;
Aggregate functions Aggregate functions skip NULL values.
Subqueries with IN SELECT * FROM Scores WHERE A IN ( SELECT B FROM Scores);
Subqueries with NOT IN SELECT * FROM Scores WHERE A NOT IN ( SELECT B FROM Scores);

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture