IN Special Operator

One of the most frequently used nested query comparison tests determines if a value retrieved by a query is IN (or NOT IN) the set of data values retrieved by a lower level subselect. The following sample query retrieves the names of suppliers who carry part number "P2":

SELECT sname
FROM supplier
WHERE sno IN
  SELECT sno
  FROM partsupp
  WHERE pno = "P2"

The inner (or lower) query is referred to as a nested query (or subselect) since it is located within an outer query statement. To execute the nested query, the system first tests for the PNO (part number) column values of records located in the PARTSUPP table that match "P2". The subselect statement (or subselect) next retrieves SNO (supplier number) column values for the records containing the correct part number.

The results of this first nested subselect appear below:

The final result (shown below) is then produced by the outer query, which retrieves the four SNAME column values from the SUPPLIER table corresponding to the supplier numbers retrieved in the inner query.

Note that the last nested query could have been written as a table join, as follows:

SELECT sname
  FROM supplier, partsupp
  WHERE supplier.sno = partsupp.sno
    AND partsupp.pno = "P2" 

The processing that occurs for a table join is very similar to that occurring during a nested query execution. This processing consists of selecting a subset of records from one of the tables that meet all conditions except the join condition. The records in the other table are then tested for the join condition against the first subset of records.

The major difference between processing a join and a nested query is that the XDB Server can perform query optimization when a join is executed. For example, query optimization allows the system to decide which table to process first to achieve the most efficient performance. When a nested query is executed, however, the innermost query is always performed first.

Algorithms exist for transforming nested queries into joins (or flat queries) before processing. However, these algorithms are complex and time consuming, and therefore have not been implemented by XDB Server.

When nesting queries, the boolean operator NOT can be included to select those records that do not meet the test conditions set forth in selection criteria. For example, the following query returns the SNO and SNAME column values for all suppliers not found in the PARTSUPP table:

SELECT sno, sname
FROM supplier
WHERE sno NOT IN
  SELECT sno
  FROM partsupp

Supplier S5 (or "ADAMS") is the only supplier that meets this condition.

Parentheses can be added to complex SQL statements to enhance clarity or to change the execution order of the various subqueries and table joins. For example, the following statement performs a series of XDB Server operations equivalent to the descriptive text summary below:

Find all parts carried by suppliers, where the suppliers either have a status of 10 or are located in "PARIS".

SELECT DISTINCT pno, pname
FROM part, partsupp
WHERE part.pno = partsupp.pno
  AND sno IN
    SELECT sno
    FROM supplier
    WHERE status = 10
      OR city = "PARIS"

This last query retrieves the following results:

If parentheses are added to the previous complex query, the results are interpreted differently.

The following reformatted query statement is equivalent to the descriptive text summary below:

Find all parts carried by suppliers having a status of 10 or a part made in "PARIS".

SELECT DISTINCT pno, pname
FROM part, partsupp
WHERE part.pno = partsupp.pno
  AND sno IN
    (SELECT sno
    FROM supplier
    WHERE status = 10)
  OR city = "PARIS"

This modified query yields the following result: