where clause

Sets the search conditions in a select, insert, update, or delete statement.


Search conditions immediately follow the keyword where in a select, insert, update, or delete statement. If you use more than one search condition in a single statement, connect the conditions with and or or.

where [not] expression comparison_operator expression
where {[not] expression comparison_operator expression} | {...}
where [not] expression [not] like "match_string"
	[escape "escape_character "]
where [not] expression is [not] null
where [not] expression [not] between expression and expression
where [not] expression [not] in ({value_list | subquery})
where [not] exists (subquery)
where [not] expression comparison_operator {any | all} (subquery)
where [not] column_name join_operator column_name
where [not] logical_expression
where [not] expression {and | or} [not] expression
where column_name is [not] null




  • where and having search conditions are identical, except that aggregate functions are not permitted in where clauses. For example, this clause is legal:
    having avg (price) > 20
    This clause is not legal:
    where avg (price) > 20

    For examples, see Transact-SQL Functions in Reference Manual: Building Blocks for information on the use of aggregate functions, and group by and having clauses.

  • Joins and subqueries are specified in the search conditions; see the Transact-SQL User’s Guide for full details.

  • You can use the keyword like to search a unitext column for a specific pattern. However, the like clause is not optimized when it is used with a unitext column. like pattern matching for unitext depends on the default Unicode sort order, which is also used for like pattern matching for unichar and univarchar datatypes.

  • The where clause accepts text and unitext LOB locators, but not image LOB locators, for the variables expression and match_string.
    where expression like ‘match_string’

    When match_string is a locator, the SAP ASE server uses only up to 16KB of the corresponding LOB.

  • Specifying the null condition selects only those rows with a null value in the specified LOB column. The LOB value may be null either because it was explicitly assigned a null value, or because the LOB was not initialized.

  • The number of and and or conditions in a where clause is limited only by the amount of memory available to run the query.

  • The pattern string included in the like predicate is limited only by the size of string that can be placed in a varchar.

  • There are two ways to specify literal quotes within a char or varchar entry. The first method is to use two quotes. For example, if you began a character entry with a single quote, and you want to include a single quote as part of the entry, use two single quotes:
    'I don''t understand.'
    Or use double quotes:
    "He said, ""It's not really confusing."""
    The second method is to enclose a quote in the opposite kind of quotation mark. In other words, surround an entry containing double quotes with single quotes (or vice versa). Here are some examples:
    'George said, "There must be a better way."' 
    "Isn't there a better way?" 
    'George asked, "Isn"t there a better way?"' 
  • To enter a character string that is longer than the width of your screen, enter a backslash (\) before going to the next line.

  • If a column is compared to a constant or variable in a where clause, the SAP ASE server converts the constant or variable into the datatype of the column so that the optimizer can use the index for data retrieval. For example, float expressions are converted to int when compared to an int column. For example:
    where int_column = 2

    selects rows where int_column = 2.

  • When the SAP ASE server optimizes queries, it evaluates the search conditions in where and having clauses, and determines which conditions are search arguments (SARGs) that can be used to choose the best indexes and query plan. All of the search conditions are used to qualify the rows. For more information on search arguments, see the Performance and Tuning Guide.

See also:
  • Date and Time Datatypes in Reference Manual: Building Blocks

  • sp_helpjoins in Reference Manual: Procedures


ANSI SQL – Compliance level: Entry-level compliant.

Related reference
group by and having Clauses