Subqueries, also called nested queries or inner queries, are SELECT statements used within other SELECT phrases (called outer queries) in WHERE, HAVING and FROM clauses. They may also be used within DML statements for filtering purposes in the WHERE clause.
Subqueries generally serve for obtaining values from the database that are used to filter records in the outer queries The subquery must be always enclosed in brackets and may appear only on the right side of a comparison operator.
SELECT ... FROM ... WHERE expr operator (SELECT ... FROM ... [WHERE ...] [GROUP BY...[HAVING...]]) [GROUP BY...[HAVING...]] [ORDER BY...]
SELECT fname, lname FROM students WHERE id_stud IN (SELECT id_stud FROM grades WHERE value=10) ORDER BY lname, fname; --What does the above query return?
SELECT DISTINCT fname, lname FROM students s, grades n WHERE s.id_stud=n.id_stud and value > (SELECT MAX(value) FROM students s, grades n WHERE s.id_stud=n.id_stud AND lname='Archip') ORDER BY lname, fname; --What does the above query return?
If the subquery returns a single row, the comparison operator may be a standard one from the following: >, =, >=, <, <>, <=. If the subquery returns several rows, special operators must be used as IN, ALL, ANY, SOME.
Several subqueries may be used:
SELECT DISTINCT fname, lname FROM students s, grades n WHERE s.id_stud=n.id_stud and value > (SELECT MAX(value) FROM students s, grades n WHERE s.id_stud=n.id_stud AND lname='Archip') AND groupno= (SELECT groupno FROM students WHERE lname='Archip') ORDER BY lname, fname; --What does the above query return?
Subqueries may return more than one column, case for which the condition must be formulated over tuples:
SELECT DISTINCT year, groupno, fname, lname, value FROM students s, grades n WHERE s.id_stud=n.id_stud and (groupno,year,value) IN (SELECT groupno,year, MAX(value) FROM students s, grades n WHERE s.id_stud=n.id_stud GROUP BY groupno, year) ORDER BY year, groupno; --What does the above query return?
Limiting the number of rows returned by a query: the ROWNUM pseudocolumn
Sometimes we need to limit the number of rows returned by a query. A common example is returning "top n" rows. For our database we could ask for "the first three students ordered descending by their average grades"
In some DBMSs this problem could be solved by using the LIMIT keyword. However, in Oracle we can limit the number of rows with the aid of the ROWNUM pseudocolumn.
ROWNUM is a column dynamically generated when the query is processed. To be able to correctly use it, one must understand exactly the moment when its values are assigned. For a complex query containing all the taught clauses, the processing order is as follows:
- the records are brought from the tables appearing in the FROM clause and are filtered to satisfy the conditions in the WHERE clause;
- ROWNUM is assigned for the first row and then is incremented;
- the projection operator corresponding to the SELECT clause is applied;
- groups are created as specified by the GROUP BY clause;
- filters are applied as specified by the HAVING option;
- the rows are finally ordered if ORDER BY is present.
The order above explains why
SELECT * FROM students WHERE ROWNUM>1;
does not return any line and
SELECT * FROM students WHERE ROWNUM<4 ORDER BY lname, fname;
does not return the first three students in the alphabetical ordering, as one could expect.
The way we can obtain the first three students in the alphabetical ordering is to first ensure the correct ordering of the rows and than limit the number of rows returned:
SELECT * FROM (SELECT * FROM students ORDER BY lname, fname) WHERE ROWNUM<4;
- Show the last name for the students earning the smallest scholarship.
- Show the last name for the students that are colleagues with a student called Arhire (coleague = same year and same group).
- For each group show the name for the students with the lowest grade in their group.
- Identify the students whose average grade is higher than the average of all the grades in the database. Show their names and their average grades.
- Show the last_name and the average grade for the best three students (as decided by their average grades).
- Show the name(s) and the average grades for the student(s) having the highest average grade in the database(CAUTION: limiting the number of rows may eliminate students on equal positions; find an alternative correct solution).
- Show the first and last names for the students receiving the same grade as student Ciprian Ciobotariu at Logics. Exclude student Ciprian Ciobotariu from the list.