Subqueries - speaker notes
A subquery is a SELECT within a SELECT (also called nested, sub and inner).
The inner SELECT is executed first and the results are used to resolve the outer SELECT.
Note that Joanne Brown's salary of 48000 and Richard Jones' salary of 50000 did not qualify them to be included in the list.
This kind of select would be used when I wanted to find out all employees that make less than a particular employee. I have to know how much they make first, then I can find everyone who makes less.
If the WHERE close on the outer SELECT had said salary <=
then Joanne Brown and her salary of 48000 would have been included.
Notice that the IN allows the inner select to return multiple values while the comparison on the previous page only allowed one value to be returned.
Error caused by an inner select that came back with multiple values for salary and an outer SELECT that expected only one value to compare against.
Another example where the inner SELECT returns only one value so the <= work.
Group functions like SUM, AVG, MAX, MIN etc. return one value and are therefore useful in this subquery structure.
The inner select looks for a match to MAN or MAR in the stucoure00 table and find the studentidno for records that match. The outer select then find the records in the student00 table that have the retrieved studentidno.
This slide combines grouping, and the use of the HAVING clause in conjunction with the grouping.
Note this particular example happens to use the same table for both the inner and outer selects.
This shows two inner selects in an AND relationship. Therefore, both will be applied to the outer select and the records shown will meet both criteria.
NOTE: The where in the outer select uses city which is repeated in the SELECT of the first inner select. The where in the outer select also uses majorcode which is repeated in the SELECT of the second inner select.
This is the SELECT from the previous slide with one major change, the AND between the two inner selects has been changed to an OR.
NOTE: The column used in the WHERE clauses, city and majorcode match the columns used in the SELECT of the two inner selects.
The first inner select takes information from two tables where there is a link on coursecd. It groups the students by studentidno and determines the sum of their credits. Only those with more than 3 credits are sent back to the outer select.
NOTE: When the column listed in the WHERE studentidno IN matches the column listed in the SELECT studentidno.
ALL is comparing against ALL of the numbers returned. In this example, the outer select is looking for salaries that are greater than ALL of the averages that were returned. 48000 and 50000 are the only ones greater than all four salaries returned by the inner select.
ANY means that if the bonus is >= to ANY of the number selected it will appear on the list.