SQL conditions

SQL conditions are used:

• In WHERE and HAVING clauses in queries and subqueries.
• In ON clauses in join constructs.

Syntax

USoft supports all industry-standard SQL condition constructs. These syntax options are an integral part of the standard SQL language. There are no USoft extensions in this area.

condition-list       ::=  condition  logical-connector  condition ...

condition            ::=  {     operand  comparison-operator  operand    |
                           NOT( operand  comparison-operator  operand )  }

operand              ::=  {  column-expression | literal  }

comparison-operator  ::=  {  math-operator  |  sql-operator  }
math-operator        ::=  {  =  |  !=  |  >  |  >=  |  <  |  <=  |  <>  }
sql-operator         ::=  {  
                                { LIKE  |  IN  |  EXISTS  |  BETWEEN } |
                            NOT { LIKE  |  IN  |  EXISTS  |  BETWEEN } |

                            IS NULL |
                            IS NOT NULL
                          }

logical-connector    ::=  {  AND  |  OR  }

The syntax of sql-operator constructs is not detailed above. These constructs are all different: LIKE calls for a wildcard pattern and has an optional ESCAPE clause, IN calls for an enumeration, EXISTS for a subquery, and BETWEEN for two column expressions connected by AND, while IS NULL is an operator for comparing a value with the NULL value. See SQL operators for details.

Example 1

This condition matches ARISTO products with an ID not higher than 6000 produced after February 1, 2022:

      product_name = 'ARISTO'
AND   product_id   <= 6000
AND   production_date  >  CHAR_TO_DATE( 'DD-MON-YYYY', '01-FEB-2022' )

Example 2

This condition matches ARISTO products except all ARISTO products from Australia and except all ARISTO products of type Consumer:

        product_name = 'ARISTO'
AND NOT ( country = 'AUSTRALIA' OR type = 'CONSUMER' )

Example 3

This conditions matches all tours to Brazil that do not have a guide:

t.destination = 'BRAZIL'
AND NOT EXISTS (
    SELECT   ''
    FROM     person  p
    WHERE    p.person_id = t.guide_id
)