Oracle中NULL的用法:
参考网址:
http://www.oratechinfo.co.uk/nulls.html#null_def
Using NULLs
- Definition of NULL
- Using NULL in a mathematical expression always returns NULL
- Appending NULL to a non-null string does not affect the string
- Aggregate functions tend to "ignore" NULLs
- GROUP BY treats NULL as a seperate "value"
- NULL has to be tested using the IS [NOT] NULL clause
- Completely NULL values will not be indexed
- Default Datatype of NULL is VARCHAR2
- NULLs in Constraints
- NULL is NOT CHR(0)!
- NULLs affect NOT IN expressions
- LIKE ignores NULLs
Definition of NULL
NULL is probably the most controversial database (not just Oracle) construct / concept ever. E.F.Codd, inventor of relational theory, defines it as :
Rule 3: Systematic Treatment of Null Values
Null values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational DBMS for representing missing information in a systematic way, independent of data type.
The problem is when you try and use them. It's amazing how many (even experienced) developers forget these basic tenets.
1. Using NULL in an expression always returns NULL.
SQL> SELECT 1 + NULL FROM DUAL;
1+NULL
----------
1 row selected.
2. Appending NULL to a non-null string does not affect the string.
SQL> SELECT 'XXX' || NULL FROM DUAL; 'XX --- XXX 1 row selected.
3. Aggregate functions tend to "ignore" NULLs.
SQL> SELECT a
2 FROM t;
A
----------
10
<-- NULL value
2 rows selected.
SQL> SELECT SUM(a), AVG(a), MAX(a), MIN(a), COUNT(a), COUNT(*)
2 FROM t;
SUM(A) AVG(A) MAX(A) MIN(A) COUNT(A) COUNT(*)
---------- ---------- ---------- ---------- ---------- ----------
10 10 10 10 1 2
1 row selected.
All aggregate functions ignore the NULL entry except, of course, COUNT(*).
4. GROUP BY treats NULL as a seperate "value"
SQL> SELECT a, COUNT(a), COUNT(*)
2 FROM t
3 GROUP BY a;
A COUNT(A) COUNT(*)
---------- ---------- ----------
10 1 1
0 1
2 rows selected.
5. NULL has to be tested using the IS [NOT] NULL clause
NULL is NOT equal to NULL, nor is it equal to NULL. NULL implies "unknown", so it is "unknown" if NULL is equal to NULL, or NOT equal to NULL, hence you have to test for "nullity" via the IS NULL or IS NOT NULL clause, i.e.
/* An example to show that trying to equate NULL via any method will not work */
SQL> SELECT 1
2 FROM dual
3 WHERE NULL <> NULL
4 OR NULL != NULL
5 OR NULL = NULL
6 OR NULL IN ( SELECT NULL FROM DUAL )
7 OR NULL NOT IN ( SELECT 'X' FROM dual );
no rows selected
SQL> SELECT 1
2 FROM dual
3 WHERE NULL IS NULL;
1
----------
1
1 row selected.
Note, that DECODE assumes that a NULL is the same as another NULL, i.e.
SQL> SELECT DECODE(NULL, NULL, 'NULL', 'NOT NULL') FROM dual;
DECO
----
NULL
1 row selected.
6. Completely NULL values will not be indexed
Think of it like this, when you
CREATE INDEX t_idx ON t(a,b)
it is "as if" it were creating the index on a || b.
If the result of a || b Is NOT NULL -- an entry will be made in the index structure. If it is NULL, it will not be in the index.
NULL columns may be stored in an index (a b*tree index) however, entirely NULL index entries are NOT stored in the b*tree.
Proof :
SQL> CREATE TABLE t ( a VARCHAR2(10), b VARCHAR2(10) );
Table created.
SQL> INSERT INTO t VALUES ( 'X', 'X' );
1 row created.
SQL> INSERT INTO t VALUES ( 'X', NULL );
1 row created.
SQL> INSERT INTO t VALUES ( NULL, 'X' );
1 row created.
SQL> INSERT INTO t VALUES ( NULL, NULL );
1 row created.
SQL> CREATE INDEX t_ind ON t ( a,b );
Index created.
SQL> ANALYZE INDEX t_ind VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height, lf_rows, lf_blks, br_rows, br_blks, used_space, rows_per_key
2 FROM index_stats;
HEIGHT LF_ROWS LF_BLKS BR_ROWS BR_BLKS USED_SPACE ROWS_PER_KEY
---------- ---------- ---------- ---------- ---------- ---------- ------------
1 3 1 0 0 43 1
1 row selected.
Proving that 3 rows are created, all except the all NULL row.
Utilising the index for queries of the form "column IS NULL" will only use the index IF the optimiser determines that the query cannot return a row from the table where each column is NULL, i.e.
SQL> ANALYZE TABLE t COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT a, b
2 FROM t
3 WHERE a IS NULL;
A B
---------- ----------
X
2 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=4)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=2 Bytes=4)
FULL TABLE SCAN occurs since the predicate does not remove the fact that B can be NULL
SQL> SELECT a, b
2 FROM t
3 WHERE a IS NULL
4 AND b = 'X';
A B
---------- ----------
X
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=1 Card=1 Bytes=2)
INDEX scan now occurs, since B cannot be NULL
7. Default Datatype of NULL is VARCHAR2
NULL is a convention for describing the "absence" or "uncertainty" of data, isn't it? Yes and No. In Oracle, NULL still has a datatype, and by default it is VARCHAR2.
You can show this with the following example.
SQL> SELECT x, DECODE(x, 2, NULL, x)
2 FROM
3 ( SELECT 1 x FROM dual
4 UNION ALL
5 SELECT -2 x FROM dual );
X DECODE(X,2,NULL,X)
---------- ----------------------------------------
1 1
-2 -2
2 rows selected.
So, as we see, SELECTing x by itself shows it's a number (right justified), but wrapping a DECODE around it with a NULL expression left justifies it and is therefore a string. But how does this tell us that NULL is a string? Simply, because of the way that DECODE is documented to work. When dealing with the return datatype of DECODE, Oracle looks at the first return value datatype and uses that, in this case NULL. NULL must therefore default to a VARCHAR2.
The "fix"? Be explicit, i.e.
SQL> SELECT x, DECODE(x, 2, TO_NUMBER(NULL), x)
2 FROM
3 ( SELECT 1 x FROM dual
4 UNION ALL
5 SELECT -2 x FROM dual );
X DECODE(X,2,TO_NUMBER(NULL),X)
---------- -----------------------------
1 1
-2 -2
2 rows selected.
8. NULLs in Constraints
Foreign Keys
Remarkably, you can use NULL to "circumvent" foreign key constraints. This is not a feature of Oracle, but is defined in ANSI SQL-92.
Here's an example :
SQL> CREATE TABLE parent ( a VARCHAR2(10) NOT NULL,
b VARCHAR2(10) NOT NULL,
CONSTRAINT pk PRIMARY KEY (a,b) );
Table created.
SQL> CREATE TABLE child ( a VARCHAR2(10) NOT NULL,
b VARCHAR2(10),
CONSTRAINT fk FOREIGN KEY (a,b) REFERENCES parent(a,b) );
Table created.
Now, let's try inserting a row into the child table.
SQL> INSERT INTO child VALUES ('X', 'Y');
INSERT INTO child VALUES ('X', 'Y')
*
ERROR at line 1:
ORA-02291: integrity constraint (ORAUSER.FK) violated - parent key not found
We can't since there's no parent record, BUT we can insert a partially NULL value........
SQL> INSERT INTO child VALUES ('X', NULL );
1 row created.
Oracle's documentation (referenced above) mentions that the way to prevent this behaviour is to use NOT NULL or CHECK constraints.
Unique Constraints
Unique constraints will accept entries which are completely NULL, but partial NULLs are rejected, i.e.
SQL> CREATE TABLE t ( a VARCHAR2(10), 2 b VARCHAR2(10), 3 CONSTRAINT t_uniq UNIQUE (a,b) ); Table created.
Try and insert two rows which are partially NULL, and it is rejected, i.e.
SQL> INSERT INTO t VALUES ('X', NULL);
1 row created.
SQL> /
INSERT INTO t VALUES ('X', NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (ORAUSER.T_UNIQ) violated
Try and insert rows which are completely NULL, and they're accepted, i.e.
SQL> INSERT INTO t VALUES (NULL, NULL); 1 row created. SQL> / 1 row created.
Primary Keys
This is not a problem with Primary Keys, since by definition columns in a Primary Key, are NOT NULL, i.e.
SQL> CREATE TABLE t ( a VARCHAR2(10), 2 CONSTRAINT t_pk PRIMARY KEY (a) ); Table created. SQL> DESC t; Name Null? Type ----------------------------------------- -------- ---------------------------- A NOT NULL VARCHAR2(10)
9. NULL is NOT CHR(0)!
ASCII charts define CHR(0) as null, but this is not the same as Oracle's NULL, i.e.
SQL> SELECT 1 2 FROM dual 3 WHERE CHR(0) IS NULL 4 / no rows selected
CHR(0) is therefore NOT NULL, i.e.
SQL> SELECT 1
2 FROM dual
3 WHERE CHR(0) IS NOT NULL
4 /
1
----------
1
*DEV NOTE*
You can use the DUMP command to see the internal structure of data, and this is further proof of the difference between the two :
SQL> SELECT * 2 FROM ( SELECT 'NULL', DUMP(NULL) 3 FROM dual 4 UNION ALL 5 SELECT 'CHR(0)', DUMP(CHR(0)) 6 FROM dual ) 7 / 'NULL' DUMP(NULL) ------ -------------- NULL NULL CHR(0) Typ=1 Len=1: 0
10. NULLs affect NOT IN expressions
You have to be extremely careful when dealing with NULLs when they occur as the result of a NOT IN operation (either subquery or value list).
For example, the outcome of the following query is what you'd expect :
SQL> SELECT * 2 FROM dual 3 WHERE 'x' IN ( NULL, 'x' ); D - X
But, the following query does not behave maybe as you'd expect :
SQL> SELECT * 2 FROM dual 3 WHERE 'x' NOT IN ( NULL, 'x' ); no rows selected
The "problem", however, often catches developers out when dealing with subqueries. Let's populate a table with a NULL and a NOT NULL value :
SQL> INSERT INTO t (a) VALUES (NULL);
1 row created.
SQL> INSERT INTO t (a) VALUES ('x');
1 row created.
Now, let's try querying on this table in a subquery :
SQL> SELECT * 2 FROM dual 3 WHERE 'x' IN ( SELECT a FROM t ); D - X
OK, as we'd expect, but watch what happens with NOT IN and a value that isn't in the subquery result set :
SQL> SELECT * 2 FROM dual 3 WHERE 'y' NOT IN ( SELECT a FROM t ); no rows selected
Why did this happen?
It's actually to do with an earlier "rule", i.e. the issue of checking whether a value IS or IS NOT NULL.
The optimiser "rewrites" IN expressions in the following way :
x IN ( value1, value2 ) as WHERE x = value1 OR x = value2
So, as you can see, the existence of the OR ensures that if any of the "values" are NULL, then it doesn't affect the checking of the others.
HOWEVER, a NOT IN expression is evaluated as :
x NOT IN ( value1, value2 ) as WHERE x != value1 AND x != value2
Now, you should be able to see why NULL values affect this resultant expression, if ANY of the values are NULL, the whole expression returns "false", and hence no rows.
Therefore, you can say with certainty that any expression utilising NOT IN must not have any values which are NULL, otherwise, no rows will be returned / it will return "false", and is almost certainly a bug in your code.
11. LIKE ignores NULLs
As mentioned above, the only way of searching for NULL values is to use the IS NULL clause. LIKE cannot return NULL values, even on an "open" query, i.e.
SQL> CREATE TABLE t ( a VARCHAR2(10) );
Table created.
SQL> INSERT INTO t VALUES (NULL);
1 row created.
SQL> INSERT INTO t VALUES ('x');
1 row created.
SQL> SELECT *
2 FROM t
3 WHERE a LIKE '%';
A
------------------------------
x
1 row selected.