IN operator form to check for matching with one of the query results

Following is an example of the use of such operator:

Example:

// Select goods names that were included in invoices
SELECT
  Articles.Description
FROM
  Catalog.Articles AS Articles
WHERE
  Articles.Ref IN
     (
SELECT
       InvoiceContent.Article
FROM
        Document.Invoice.Content AS InvoiceContent
     )

Query result:

Goods

Faucet

Plunger

Table

Chair

To obtain inverse result (that is, if you need to determine that the value doesn't match any of the query results) you should use the following query:

Example:

// Select goods names that were included in invoices
SELECT
  Articles.Description
FROM
  Catalog.Articles AS Articles
WHERE
  Articles.Ref NOT IN
     (
SELECT
       InvoiceContent.Article
FROM
        Document.Invoice.Content AS InvoiceContent
     )

Query result:

Goods

Sanitaryware

Furniture

Please note that from IN operation query you can call table fields that encountered in external query before the operation.

Example:

// Select goods names that were included in invoices
SELECT
  Articles.Description
FROM
  Catalog.Articles AS Articles
WHERE
  Articles.Ref IN
     (
SELECT
       InvoiceContent.Article
FROM
        Document.Invoice.Content AS InvoiceContent
WHERE
       InvoiceContent.Article = Articles.Ref
     )

Query result:

Goods

Faucet

Plunger

Table

Chair