Creating Subqueries

You can use the results of one query as the input for another. Typically, you use the results of a subquery as a search condition that uses the IN( ) function or EXISTS operator. However, you can also use a subquery in the FROM clause.

You can create a subquery by entering it in either the Grid pane or SQL pane.

To define an EXISTS subquery in the Grid pane

1. Create the primary query.
   
   
2. In the Column column for the first empty row in the Grid pane, enter EXISTS followed by the subquery in parentheses.
   
   
3. In the Criteria column for the row containing the subquery, enter TRUE, FALSE, =TRUE, or =FALSE. Entering FALSE or =FALSE results in a NOT EXISTS query.

Note   To create a NOT EXISTS query, create an EXISTS query as listed in the above steps, and set the Criteria column to FALSE. If you enter NOT EXISTS in the Grid pane, the Query Designer will display an error.

To define a subquery in the SQL pane

1. Create the primary query.
   
   
2. In the SQL pane, select the SQL statement, and then use Copy to move the query to the Clipboard.
   
   
3. Start the new query, and then use Paste to move the first query into the new query's WHERE or FROM clause.

For example, imagine you have two tables, products and suppliers, and you want to create a query showing all products for suppliers in Sweden. Create the first query on the suppliers table to find all Swedish suppliers:

SELECT supplier_id
FROM supplier
WHERE (country = 'Sweden')

Use the Copy command to move this query to the Clipboard. Create the second query using the products table, listing the information you need about products:

SELECT product_id, supplier_id, product_name
FROM products

In the SQL pane, add a WHERE clause to the second query, then paste the first query from the Clipboard. Place parentheses around the first query, so that the end result looks like this:

SELECT product_id, supplier_id, product_name
FROM products
WHERE supplier_id IN
   (SELECT supplier_id
  FROM supplier
  WHERE (country = 'Sweden'))

Note   When you add a subquery to the WHERE clause, the subquery appears in the Criteria column of the Grid pane. You can edit it further in either the Grid pane or SQL pane. However, the tables and table-structured objects, columns, and expressions referenced in the subquery are not displayed in the Diagram or Grid pane.

See Also

Supported Query Types