The Keyword NULL in Cast Expressions

Cast expressions can appear in the projection list, including expressions of the form NULL::datatype, where datatype is any data type known to the database:

The keyword NULL has a global scope of reference within expressions. In SQL, the keyword NULL is the only syntactic mechanism for accessing a NULL value. Any attempt to redefine or restrict the global scope of the keyword NULL (for example, declaring an SPL variable called null) disables any cast expression that involves a NULL value. Make sure that the keyword NULL receives its global scope in all expression contexts.

Column Expressions

A column expression specifies a data value in a column in the database, or a substring of the value, or (for Dynamic Server only) a field within a ROW-type column. This is the syntax for column expressions.

Column Expressions:
 
|--+-+-table.---+--+-column--+---------------------------+---+-+--|
   | +-view.----+  |         |  (1)                      |   | |
   | +-synonym.-+  |         '--------[-- first, last--]-'   | |
   | '-alias.---'  |  (2)    (1)                             | |
   |               +---------------ROWID---------------------+ |
   |               '-row_column--+-------------------------+-' |
   |                             +-.*----------------------+   |
   |                             | .---------------------. |   |
   |                             | V  (3)                | |   |
   |                             '----------. field_name-+-'   |
   |  (2)                                                      |
   '--------row_col_expr--+-------------------------+----------'
                          +-.*----------------------+
                          | .---------------------. |
                          | V  (3)                | |
                          '----------. field_name-+-'

Notes:

Informix extension
Dynamic Server only
Use path no more than three times

Element	Description	Restrictions	Syntax
alias	Temporary alternative name for a table or view, declared in the FROM clause of a query	Restrictions depend on the clause of the SELECT statement in which alias occurs	Identifier, p. Identifier
column	Name of a column	Restrictions depend on the SQL statement where column occurs	Identifier, p. Identifier
field_name	Name of a ROW field in the ROW column or ROW-column expression	Must be a member of the row that row-column name or row_col_expr or field name (for nested rows) specifies	Identifier, p. Identifier
first, last	Integers indicating positions of first and last characters within column	The column must be of type CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, or TEXT, and 0 < first ≤ last	Literal Number, p. Literal Number
row_col_expr	Expression that returns ROW-type values	Must return a ROW data type	Expression, p. Expression
row_column	Name of a ROW-type column	Must be a named ROW data type or an unnamed ROW data type	Identifier, p. Identifier
synonym, table, view	Table, view, or synonym (for the table or view) that contains column	Synonym and the table or view to which it points must exist	Database Object Name, p. Database Object Name

The following examples show column expressions:

company 

items.price 

cat_advert [1,15]

You must qualify the column name with a table name or alias whenever it is necessary to distinguish between columns that have the same name but are in different tables. The SELECT statements that the following example shows use customer_num from the customer and orders tables. The first example precedes the column names with table names. The second example precedes the column names with table aliases.

SELECT * FROM customer, orders 
   WHERE customer.customer_num = orders.customer_num;

SELECT * FROM customer c, orders o
   WHERE c.customer_num = o.customer_num;