Search Conditions in Sybase SQL Statements
Implementing Database in Sybase Part 6
Division 4
Forward: In this part of the series, we look at search conditions in the Sybase SQL Anywhere SQL Statements.
By: Chrysanthus Date Published: 26 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Comparison Operators
You can use comparison operators in the condition of the CHECK constraint. The comparison operators and there meanings are:
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
<> not equal to; same as != below
!= not equal to
!< not less than
!> not greater than
An example of a condition is:
price > 0
where price is the column name for price. In the column-definition when creating a table, you would type this in the CHECK constraint as,
CHECK (price > 0)
This means, each price value in the price column in the table should always be greater than zero (it is not the alphabet O).
The syntax to use comparison operators is,
expression comparison-operator expression
For simplicity, consider the word, expression here as a column name or a value (e.g. zero above). It can actually mean a peace of code.
In the SQL Anywhere manual, it is stated that The NULL value specifies a value that is unknown or not applicable. For simplicity, consider a NULL value as an empty cell in a table. Examples of ways you can use NULL in conditions are as follows, where salary is the name of a column:
Salary = NULL
Salary <> NULL
NOT (Salary = NULL) means the opposite of salary is equal to NULL
NOT (Salary <> NULL) means the opposite of salary is not equal to NULL
Salary = 1000
Salary IS NULL
Salary IS NOT NULL
Here, NULL, NOT, and IS are reserved words. In the second and third lines above, the NOT produces the opposite of what is in the brackets.
Syntax of Search Condition
I will now give you the full syntax of the search condition. You may read it and not understand, but just glance through it. In the syntax, | means and/or; anything in a square bracket is optional; consider the word, expression as a column name or a value; {} is used for grouping. The complete syntax is:
expression comparison-operator expression
| expression comparison-operator { [ ANY | SOME ] | ALL } ( subquery )
| expression IS [ NOT ] DISTINCT FROM expression
| expression IS [ NOT ] NULL
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] LIKE pattern [ ESCAPE expression ]
| expression [ NOT ] SIMILAR TO pattern [ ESCAPE escape-expression ]
| expression [ NOT ] REGEXP pattern [ ESCAPE escape-expression ]
| expression [ NOT ] IN ( { expression
| subquery
| value-expression1 , ... } )
| CONTAINS (column-name [,... ] , query-string )
| EXISTS ( subquery )
| NOT condition
| search-condition [ { AND | OR } search-condition ] [ ... ]
| ( search-condition )
| ( search-condition , estimate )
| search-condition IS [ NOT ] { TRUE | FALSE | UNKNOWN }
| expression IS [ NOT ] OF ( [ ONLY type-name ,... )
| trigger-operation
We shall see the use of everything in this tutorial as we go along in the series. We end here and continue in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer A Jack of all Trade Poem
NEXT