Java Reference
In-Depth Information
They are handled by defining an escape character at the end of the query in which
the characters are used. The escape character is defined in curly braces ({}) using
the keyword escape:
{escape 'escape-character'}
For example, the following query finds names that begin with an underscore. It
uses the backslash (\) character as an escape character:
SELECT name
FROM variables
WHERE Id LIKE `\_%' {escape '\'};
Using Subqueries
The use of queries is not limited to situations where you want to return a result set to
the user. It is frequently useful to create a result set so that you can use it within
another SQL statement. A
subquery
is used as part of another SQL statement.
Subqueries can be nested inside any of the following types of SQL statements:
SELECT or SELECT...INTO
INSERT...INTO
DELETE
UPDATE
Inside another query or subquery
Subqueries are used to provide an intermediate result set to be operated on by
another part of the SQL statement. For instance, you can use a subquery to return
cost data about all the cookies in yo ur inventory and then use this cost data with a
comparison operator in another query. In this case, you use a SELECT statement to
provide a set of values to be evaluated in the WHERE or HAVING clause of the main
statement.
Subqueries can be used in WHERE or HAVING clauses as the right-hand side of the
following comparison and expressions:
Comparisons using ANY, ALL or SOME
Expressions using IN or NOT IN
Expressions using EXISTS or NOT EXISTS
Using the ANY, SOME, and ALL operators
