Building a Correlated SubSelect clause

A correlated sub-select is similar to a SubSelect, except that the subquery references columns in the main query tables.

Example: the following query displays orders where the quantity ordered for a part is greater than the stock level for that part.

SELECT * FROM Order_Line ord

WHERE Part_Number in (SELECT Part_Number from Part_Details part WHERE ord.Quantity_Ordered > part.Quantity_On_Hand)

The sub-select (using table Part_Details) references column Quantity_Ordered in table Order_Line in the main query.

EXISTS operator

When using a sub-select, in particular a correlated sub-select, the EXISTS operator is often used. This return True if the sub-select returns one row or more, otherwise returns False.

Example: to display Customers who have at least one Order, one would code:

SELECT * FROM Customer_Details a

WHERE EXISTS (SELECT 1 FROM Order_Details b WHERE a.cust_code = b.cust_code)

Note that the sub-select can only return one column. It is irrelevant what value is returned - EXISTS only checks whether any rows are returned. It is common, therefore, to just specify a constant in the column list. The above query uses 1.

To code a query such as this:

• in the Build Where clause window, select an Operator of Other and select EXISTS from the dropdown list
• click on Build SubSelect to build the subselect. You can either select a Column, or use a Function of 1.

The operator NOT EXISTS is also commonly used and return the opposite of EXISTS - eg. True if the sub-select return no rows.