René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

where clause [Oracle SQL]

where condition

Types of conditions

Comparison

Floating point

Logical

Model

Multiset

nested-table is a set
nested-table is not a set

nested-table is a empty
nested-table is not a empty

nested-table member of nested-table-2
nested-table not member of nested-table-2

nested-table submultiset of nested-table-2
nested-table not submultiset of nested-table-2

Pattern matching

See like and regexp_like

Range

expr between expr-1 and expr-2

expr not between expr-1 and expr-2
See also between.

Null

expr is null

expr is not null

XML

equals_path and under_path... needs to be finished ...

Compound

(condition)

not condition

condition-1 and condition-2

condition-1 or condition-2

Exists

exists (subquery)

In

expr in (expr-1, expr-2,... expr-n)
expr not in (expr-1, expr-2,... expr-n)

expr in (subquery)
expr not in (subquery)

(expr-1, expr-2.... expr-n) in (expr-a, expr-b,... expr-q)
(expr-1, expr-2.... expr-n) not in (expr-a, expr-b,... expr-q)

(expr-1, expr-2.... expr-n) in (subquery)
(expr-1, expr-2.... expr-n) not in (subquery)
expr-x can also be of the form (a,b,...d): 
select count(*) from dual where (1,2) in ( (1,3), (1,4) );




select count(*) from dual where (1,3) in ( (1,3), (1,4) );









Is of type




expr is of (type-name)
expr is of (type-name, type-name-2...)

expr is not of (type-name)
expr is not of (type-name, type-name-2...)

expr is of (type-name)
expr is of (type-name, type-name-2...)

expr is not of (only type-name)
expr is not of (only type-name, type-name-2...)







Appearance



    

      The where-clause appears as part in the merge SQL statement.