RE: DAV object model expressed as tables

> I have heard rumours of an "in" operator?

Yes, you have.
Let me put the rumors to rest once and for all :)
Yes, there is an IN operator in SQL 92.

> Does this handle it?

No, it doesn't handle your example as I understand it.

Now to satisfy your curiosity about the IN operator:

There are two main uses for the IN operator:
(1) As a shorthand for writing a disjunction of equality
tests: A property in the current row under scan
is tested against a list of constants. (In addition
to less writing, in some implementations you will
get you results faster if you use IN than if
you express all the OR conditions.)
(2) As a test to see if the value of a property
in the row under scan exists in the result set of
some subquery. Only one column can be returned
by the subquery, and the datatype of that column
must match the datatype of the property in question.

Example schema (this schema syntax isn't SQL syntax):

Documents: TABLE
    (
        DocType:  integer;
        Author:   string;
        Title:    string;
        ...
    );

EXAMPLE 1:

SELECT * FROM Documents
WHERE Documents.Title LIKE "%Judy%" AND 
      Documents.DocType IN (3, 6, 9)

This is equivalent to:

SELECT * FROM Documents
WHERE Docments.Title LIKE "%Judy%" AND 
      (Documents.DocType = 3 OR
      Documents.DocType = 6 OR
      Documents.DocType = 9)

EXAMPLE 2:

SELECT * FROM Documents AS D1
WHERE D1.Title LIKE "%Judy%" AND D1.DocType IN 
    (SELECT DocType FROM Documents AS D2
        WHERE D2.Author LIKE "%Joe%")


Alan Babich

Received on Tuesday, 8 September 1998 18:35:43 UTC