Qualified comparisons
Derby Reference Manual
101
10. A dynamic parameter is allowed as an item in the values list or select list of an
INSERT statement. The type of the dynamic parameter is assumed to be the type of
the target column.
INSERT INTO t VALUES (?)
-- dynamic parameter assumed to be the type
-- of the only column in table t
INSERT INTO t SELECT ?
FROM t2
-- not allowed
11. A ? parameter in a comparison with a subquery takes its type from the expression
being selected by the subquery. For example:
SELECT *
FROM tab1
WHERE ? = (SELECT x FROM tab2)
SELECT *
FROM tab1
WHERE ? = ANY (SELECT x FROM tab2)
-- In both cases, the type of the dynamic parameter is
-- assumed to be the same as the type of tab2.x.
12. A dynamic parameter is allowed as the value in an UPDATE statement. The type of
the dynamic parameter is assumed to be the type of the column in the target table.
UPDATE t2 SET c2 =? -- type is assumed to be type of c2
13. Dynamic parameters are allowed as the operand of the unary operators - or +. For
example:
CREATE TABLE t1 (c11 INT, c12 SMALLINT, c13 DOUBLE, c14 CHAR(3))
SELECT * FROM t1 WHERE c11 BETWEEN -? AND +?
- The type of both of the unary operators is INT
-- based on the context in which they are used (that is,
-- because c11 is INT, the unary parameters also get the
-- type INT.
14. LENGTH allow a dynamic parameter. The type is assumed to be a maximum length
VARCHAR type.
SELECT LENGTH(?)
15. Qualified comparisons.
? = SOME (SELECT 1 FROM t)
-- is valid. Dynamic parameter assumed to be INTEGER type
1 = SOME (SELECT ? FROM t)
-- is valid. Dynamic parameter assumed to be INTEGER type.
16. A dynamic parameter is allowed as the left operand of an IS expression and is
assumed to be a boolean.
Once the type of a dynamic parameter is determined based on the expression it is in,
that expression is allowed anywhere it would normally be allowed if it did not include a
dynamic parameter.
JOIN operations
The JOIN operations, which are among the possible
, perform joins between two tables. (You can also perform a join between two
tables using an explicit equality test in a WHERE clause, such as "WHERE t1.col1 =
t2.col2".)
Syntax
JOIN Operation
