Operations and Expressions

Part:   1  2   3 

(Continued from previous part...)

Predicate Operations

Predicate operations are operations that can be evaluated to give a condition of true or false:

1. "Comparison" Predicates: Compares two values of the same type according to a predefined order. There are 6 comparison operations:

• Equal: value = value
• Not Equal: value <> value
• Less Than: value < value
• Greater Than: value > value
• Less Than or Equal: value <= value
• Greater Than or Equal: value >= value

2. "Between" Predicates: Check a given value against a value range. There are 2 "between" operations:

• Between: value BETWEEN value AND value
• Not Between: value NOT BETWEEN value AND value

3. "In" Predicates: Check a given value against a list of values. There are 2 "in" operations:

• In: value IN (value, value, ..., value)
• Not In: value NOT IN (value, value, ..., value)

4. "Like" Predicates: Check a given character string against a character pattern. By default "Like" operations are not case sensitive. There are 2 "Like" operations:

• Like: string LIKE pattern
• Not Like: string NOT LIKE pattern

"pattern" is a character string with wildcard characters and escape sequences:

• "_": Representing one and only one single character.
• "%": Representing zero, one and many characters.
• "\_": Representing character "_".
• "\%": Representing character "%".

Examples:

'abc' LIKE 'Abc' -- true
'3.14' LIKE '_.__' -- true
'3.14159' LIKE '3.%' -- true
'herong_yang' LIKE 'herong\_yang' -- true
'4.75%' LIKE '_.__\%' -- true

5. "Null" Predicates: Check a given value against null value. There are 2 "Null" operations:

• Null: value IS NULL
• Not Null: value IS NOT NULL

Examples:

1 IS NULL -- false
'a' IS NOT NULL -- true
NULL IS NULL -- true
NULL IS NOT NULL -- false

Date and Time Operations

The most commonly used date and time operations are:

• data_time_value + interval_value
• data_time_value - interval_value
• interval_value + data_time_value

Examples:

DATE '1999-01-01' + INTERVAL '3' MONTH
DATE '2000-03-01' - INTERVAL '1' DAY -- watch out leap years!
INTERVAL '48' HOUR + TIMESTAMP '1999-01-01 00:00:00'

(Continued on next part...)

Part:   1  2   3