#### Using sub-queries in filters

When filtering the fields of a query with the filter format, you are also enabled to use sub-queries to narrow down the result according to your requirements. The following are syntaxes that can be used in sub-queries.

Quantified predicate

A quantified predicate compares a value with a set of values.

- Syntax
  `expression----+- = --+- SOME --+-- ( subselect )
  +- <> -+ ANY ---+
  +- ! = --+ ALL ----+
  +- < --+
  +- > --+
  +- <= --+
  +- ! > --+
  +- >= --+
  +- ! < --+`

  The subselect must specify a single result column and can return any number of values, whether they are null or not.

  - When ALL is specified, the result of the predicate is:
    - True if the result of the subselect is empty, or if the specified relationship is true for every value returned by the subselect.
    - False if the specified relationship is false for at least one value returned by the subselect.
    - Unknown if the specified relationship is not false for any values returned by the subselect and at least one comparison is unknown because of a null value.
  - When SOME or ANY is specified, the result of the predicate is:
    - True if the specified relationship is true for at least one value returned by the subselect.
    - False if the result of the subselect is empty, or if the specified relationship is false for every value returned by the subselect.
    - Unknown if the specified relationship is not true for any of the values returned by the subselect and at least one comparison is unknown because of a null value.
- Examples
  `SELECT qty FROM sales WHERE qty>= ALL (SELECT qty FROM sales)
  SELECT BUYERID, ITEM FROM ANTIQUES WHERE PRICE != ANY (SELECT PRICE FROM ANTIQUES);`

EXISTS predicate

The EXISTS predicate tests for the existence of certain rows.

- Syntax
  `- [ NOT ] EXISTS--(subselect)`

  The subselect may specify any number of columns and,

  - The result is true only if the number of rows specified by the subselect is not zero.
  - The result is false only if the number of rows specified by the subselect is zero.
  - The result cannot be unknown.
- Example
  `SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')`

IN predicate

The IN predicate compares a value with a set of values.

- Syntax
  `expression----+-- [NOT] IN --+-- ( subselect )`

  In the subselect form, the subselect must identify a single result column and may return any number of values, whether null or not null.

- Example
  `SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')`

The following example explains how to apply a sub-query when filtering a field:

1. Create a query named mainin in the catalog, add the table Customers and select the following columns: Customers_Customer ID, Customer Name, Customers_City, and Customers_Region.
2. Click Filter on the Query menu to open the Search Condition dialog.
3. Click the Add Condition button to add a condition line.
4. Click beside the field text box. In the Expressions dialog, select the column Customers_Customer ID, then close the dialog.
5. Select in as the operator from the operator drop-down list.
6. Click beside the value text box. In the Expressions dialog, click the Subquery tab. Select an existing query in the catalog to be the sub-query. If you want to edit the selected query, click the Edit Subquery button. To create a new sub-query, click the New Subquery button.

   Here, we create a new query named subin, add the table Orders, select the column Orders_Customer ID, and add a condition "Ship Via=Express Delivery" in the Search Condition dialog.

7. Click OK. The sub-query subin will then be added into the value text box. Click OK to close the Search Condition dialog.

Now, the sub-query subin will be applied to the filter when you build a report that uses the Customers_Customer ID column.