SEARCH CONDITION

A Search condition consists of one or more boolean value expressions. A boolean value can either be TRUE or FALSE. Each of the condition yields a boolean value and the result of the combined boolean value expressions is used to select tuples from a table. Search condition is basically used to eliminate the tuples from the From table.

Sytax: 


Condition ( ( AND | OR ) Condition )*

List of Conditions:

Text Search Condition

Description: Use the SQL SELECT statement TEXTSEARCH to perform text searches on table columns configured for text search. The SQL SELECT statements use the TEXTSEARCH (not defined in the SQL standard) condition to search text.

Syntax:

WHERE TEXTSEARCH 
TableName [ ColumnName ] 
[ MAX_HITS ] [ OFFSET ] 
'query string'

Examples: 

  • SELECT * FROM mytable WHERE TEXTSEARCH mytable mycolumn 'mad hatter'
    The data is returned as with any other select query. DataCaster supports mixing the TEXTSEARCH condition with other conditions as well as allow the use of joins in these select statements. For example:
    SELECT * FROM mytable, othertable WHERE TEXTSEARCH mytable mycolumn 'mad hatter' AND othertable.c1 = 0;

Boolean Condition

Description: The comparison operators can be used to compare the column expressions. The keyword NOT negates the boolean value generated by the condition expression.

Syntax:

WHERE [ NOT ] 
ColumnExpression  ComparisonOperator  ColumnExpression
( ( AND | OR )
  ColumnExpression  ComparisonOperator  ColumnExpression
)*

Examples:

  • SELECT C1 FROM T1 WHERE C2 = 5;
  • SELECT C1 FROM T1 WHERE C1 > 10 AND C3 < 50;

Null Condition

Description: Null condition is used to check if a column expression evaluates to null or not.

Syntax:

WHERE [ NOT ] 
ColumnExpression IS [ NOT ] NULL

Examples:

  • SELECT C1 FROM T1 WHERE C2 IS NULL;
  • SELECT C1 FROM T1 WHERE C3 IS NOT NULL;

Between Condition

Description: Between condition is used to select tuples for which the specified column expression evaluates to a value that is in the specified range.

Syntax:

WHERE [ NOT ] 
ColumnExpression [ NOT ] BETWEEN 
ColumnExpression 
AND 
ColumnExpression

Examples:

  • SELECT C1 FROM T1 WHERE C2 BETWEEN 10 AND 20;
  • SELECT DISTINCT * FROM T1 WHERE C1 + C2 NOT BETWEEN C1 * 50 AND 500;

Exists Condition

Description: Exists is always used with Subquery. When used with Exists, instead of returning the data the subquery returns a boolean value TRUE if there are any tuples and FALSE otherwise.
A Subquery can be any SelectQuery, but without Order by Clause.

Syntax:

WHERE [ NOT ] 
EXISTS SubQuery

Examples:

  • SELECT C1 FROM T1 WHERE EXISTS ( SELECT * FROM T2 WHERE C2 > 10 );
  • SELECT C1 FROM T1 A WHERE NOT EXISTS ( SELECT * FROM T1 B WHERE A.C2 = B.C2 AND A.C3 < B.C3 );

In Condition

Description: In condition is used to get those tuples which have a value in the set of specified values. The set of values can be specified either as a list of values or as a result of subquery.
A Subquery can be any SelectQuery, but without Order by Clause.

Syntax:

WHERE [ NOT ] 
ColumnExpression [ NOT ] IN 
( ( ColumnExpression )+  |  SubQuery )

Examples:

  • SELECT * FROM T1 WHERE C1 IN ( 5, 10, C2 + C3, C3 + 10 );
  • SELECT DISNTINCT * FROM T1 WHERE C1 NOT IN ( SELECT COL1 FROM T2 WHERE COL2 < COL3 );

All/Any/Some Condition

Description: All/Some/Any condition is always used with a subquery and a comparison operator. All Condition evaluates to TRUE when the comparison operator evaluates to TRUE for all the comparisons between the column expression and the set of values retrieved from the Subquery. Any/Some condition evaluates to TRUE when the comparion operator evaluates to TRUE for atleast one comparion between the column expression and the set of values retrieved from the Subquery. Any and Some are one and the same.
A Subquery can be any SelectQuery, but without Order by Clause.

Syntax:

WHERE [ NOT ] 
ColumnExpression ComparisonOperator
( SOME | ANY | ALL ) SubQuery

Examples:

  • SELECT C1 FROM T1 WHERE C2 = ANY ( SELECT COL1 FROM T2 WHERE COL2 > 10 );
  • SELECT * FROM T1 A WHERE C2 = ALL ( SELECT B.C2 FROM T1 B WHERE A.C2 = B.C2 AND A.C3 < B.C3 );

Also Read:

ColumnExpression, ComparisonOperator.