|
|
|
|
|
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.
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:
The operator NOT EXISTS is also commonly used and return the opposite of EXISTS - eg. True if the sub-select return no rows.
