10. Understand null values in Oracle database SQL development
Welcome to reprint, reprint please indicate the source: blog.csdn.net/notbaron/ar…
Databases use special values, nullvalues, to represent unknown values. A null value is not an empty string, but a special value.
Such as:
SQL>select * from customers;
CUSTOMER_IDFIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70
You can use the IS NULL clause to check for NULL values.
Such as:
SQL>select customer_id,first_name,last_name,dob from customers where dob is null;
CUSTOMER_IDFIRST_NAME LAST_NAME DOB
———– ———- ———- ———
4 Gail Black
Null values do not display anything, and the distinction between null values and empty strings is made through ORACLE’s built-in NVL() function. The input takes two arguments. If the first argument is null, the second argument is used.
SQL>select customer_id,first_name,last_name,nvl(phone,’Unkown phone number’) asphone_number from customers;
CUSTOMER_IDFIRST_NAME LAST_NAME PHONE_NUMBER
———– ———- —————————–
1 John Brown 800-555-1211
2 Cynthia Green 800-555-1212
3 Steve White 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue Unkown phone number