Question on the IN operand of an SQL WHERE clause

["McComsey, Doug" <Do...@ca.com>]

The spec does not seem to define the IN operand of a WHERE clause of an SQL query.

It is mentioned in section 8.5.3 SQL EBNF:

  whereexp ::= propname op value |
               propname IS NULL |
               propname IS NOT NULL |
               value IN propname |         <== mentioned here
               like |
               contains |
               whereexp AND whereexp |
               whereexp OR whereexp |
               NOT whereexp |
               '(' whereexp ')'

Following that, in 8.5.4.3 WHERE, there is a description of the WHERE clause with no mention of the IN parameter at all. I cannot find a description of what it means.

My question is: Are these two clauses identical?

  WHERE 'xyz' IN NAME
  WHERE NAME = 'xyz'

Or are these:

  WHERE 'xyz' IN NAME
  WHERE NAME LIKE '%xyz%'

Doug McComsey
Computer Associates
doug.mccomsey@ca.com
 

Re: Question on the IN operand of an SQL WHERE clause

[Peeter Piegaze <pe...@gmail.com>]

Hi Doug,

The presence of the IN operator in the SQL EBNF looks like a typo. It
seems to be a relic of pre-release version of the spec.

If it *did* work it would be equivalent to =. The IN was intended to
function as an 'existentially quantified' predicate in the sense that
'x' IN p (where p is a multi-valued property) would evaluate to true
if at least one value within p is 'x'.

In was discarded because we decided to make = behave 'existentially'
in SQL, as it already does in XPath.

Thanks for finding the error.

Cheers,
Peeter





On 10/14/05, McComsey, Doug <Do...@ca.com> wrote:
>
> The spec does not seem to define the IN operand of a WHERE clause of an SQL query.
>
> It is mentioned in section 8.5.3 SQL EBNF:
>
>   whereexp ::= propname op value |
>                propname IS NULL |
>                propname IS NOT NULL |
>                value IN propname |         <== mentioned here
>                like |
>                contains |
>                whereexp AND whereexp |
>                whereexp OR whereexp |
>                NOT whereexp |
>                '(' whereexp ')'
>
> Following that, in 8.5.4.3 WHERE, there is a description of the WHERE clause with no mention of the IN parameter at all. I cannot find a description of what it means.
>
> My question is: Are these two clauses identical?
>
>   WHERE 'xyz' IN NAME
>   WHERE NAME = 'xyz'
>
> Or are these:
>
>   WHERE 'xyz' IN NAME
>   WHERE NAME LIKE '%xyz%'
>
> Doug McComsey
> Computer Associates
> doug.mccomsey@ca.com
>
>
>
>