31271 Lecture 9: Database Fundamentals Notes
Lecture 9: SQL III
Simple Subquery
• A subquery is a query within a query.
• It is used in WHERE and HAVING conditions.
• The subquery will execute once.
• Most commonly used to find the maximum or minimum
• An equivalent way to finding max or min is to use the ALL operator
o Select productdescription, productstandardprice from product_t where
productstandardprice>= ALL (Select productstandardprice from product_t);
• The IN operator will test if something is included in the list that is returned from a subquery.
• The ANY operator will test if the value is included in the list.
Rules for All and Any
• When a subquery returns more than one value:
o Any and All will be used to link the subquery and main query
• When All is used:
o Every value returned by the subquery must satisfy the comparison operation
• When Any is used:
o One or more values returned y the subquery must satisfy the comparison operation.
Join vs. Subquery
• Some queries could be accomplished by either a join or a subquery however it is up to you
which you wish to use.
find more resources at oneclass.com
find more resources at oneclass.com
