Building a SubSelect clause

A subselect is used when you wish to compare a column to the value(s) in another table.

For instance, the following query displays all customers who have ordered more than 100 items:

Select * From SAMPLE.CUSTOMER_INFO

Where CUSTOMER IN (Select CUSTOMER From SAMPLE.SALES_DETAILS Where QUANTITY_ORDERED > 100)

To build a Where clause like this, click on Build SubSelect from the Build Where Clause window.

Returning Single or Multiple Rows

The golden rule of sub-selects is:

• If your Where operator is IN or EXISTS, your sub-select can return multiple rows
• For all other operators, your sub-select can return only a single row

Example of using an Operator other than IN:

Where Quantity_Ordered = (select max(Quantity_Ordered) from SALES_DETAILS

You can build a sub-select such by specify Column of Quantity_Ordered and Function of max(:).