3.2. Datatypes and NULL Values
One of the most important aspects of the structures defined within a database, such as tables and views, is the datatype of each of the columns. Perl is a loosely typed language, whereas SQL is strongly typed. Thus, each field or value is of a given datatype that determines how values and fields are compared. For example, the mapref field within the megaliths table would not be much use if it could hold only dates!
Therefore, it is important to assign an appropriate datatype to each column. This avoids any potential confusion as to how the values stored within each column are to be interpreted, and also establishes how these values can be compared in query condition clauses.
There are several common datatypes. The most widely used of these can be grouped as follows:
- Numeric datatypes
The grouping of numeric datatypes includes types such as integer and floating point (or real) numbers. These types, depending on your database, may include FLOAT , REAL , INTEGER , and NUMBER . Numeric datatypes are compared in the obvious way; that is, the actual values are tested.
- Character datatypes
Character datatypes are used to store and manipulate textual data. Any characters whatsoever -- digits or letters -- can be stored within a character datatype.
However, if digits are stored within a character datatype, they will be treated as being a string of characters as opposed to a number. For example, they'll be sorted and ordered as strings and not numbers, so "10" will be less than "9".
Depending on your database system, there can be many different types of character datatypes such as CHAR , VARCHAR , VARCHAR2 , and so on. Most databases support at least the most basic of these, CHAR.
When being compared, character datatypes usually apply lexical ordering according to the character set being used by the database.
- Date datatypes
Most database systems implement at least one datatype that contains date information, as opposed to a character datatype containing a string representation of a date. This allows you to perform arbitrary arithmetic on date values very easily. For example, you might wish to select rows where the date field corresponds to a Monday.
When comparing dates, a later date is regarded as being greater than an earlier date. datatypes for storing times and timestamps (date plus time) are also common.
- Binary object datatypes
Binary object datatypes are a relatively recent addition to database systems and allow you to store massive unstructured chunks of data -- typically images, video, or audio clips -- within a database. The actual binary object datatypes tend to differ between databases, but usually tend to be called LOBs (large objects) if they do exist. For example, the BLOB datatype stores binary data and CLOB stores large quantities of ASCII character data. Generally, LOB types cannot be compared to one another.
- The NULL value
NULL is a special kind of value that actually has no value. It can be contained within columns and signifies that no value is contained within that column for a given row. NULL values are used where the actual value is either not known or not meaningful.
When a table is created, each column can declare to either allow or disallow NULL values, regardless of the datatype of the column.
NULL values should not be confused with the numeric value of zero. They are not the same thing. Zero means zero, whereas NULL means there is no value at all.[22]
[22]Though some databases do treat empty strings as NULL values when inserting data.
If you attempt to evaluate an expression containing a NULL value, other than with various special NULL handling functions, it will always evaluate to NULL. Comparing values to NULL should always use IS NULL and IS NOT NULL instead.[23] Be careful!
[23]A few databases, such as mSQL, do use = NULL.
The NULL value plays a part in what are called "three-valued logic" tables that are used when evaluating condition clause truth tables, as discussed later in this chapter. This allows SQL conditional expressions to either be true, false, or NULL.
 |  |  |
| 3. SQL and Relational Databases |  | 3.3. Querying Data |
Copyright © 2001 O'Reilly & Associates. All rights reserved.