Chapter 8. Joins Versus Subqueries
The purpose of this chapter is to demonstrate the use of subqueries. Subqueries may often be used as alternatives to joins. There are two main issues to consider when choosing between subqueries and joins (and other techniques for combining tables). First, you must consider how to get the information. By understanding the limitations of joins and subqueries (as well as sets and other table-combining techniques), you will increase your choices as to how to get information from the database. Second, you must also consider performance. You usually a have choice of how to get multi-table information—joins, sets, subqueries, views, and so forth. In larger databases, you need to be flexible and consider other choices if a query performs poorly and/or if the query is done often.
Although set operations logically are also viable choices for retrieving data from multiple tables, set operations (discussed in Chapter 7) are less common and usually less efficient than joins and subqueries.
Subquery with an IN Predicate
Suppose that a query requests a list of names and numbers of students (which are in the
Student table in our
Student_course database) who have made As or Bs in any course (grades are in the
Grade_report table in our
Student_course database). You can complete this query as either a subquery or a join. As a subquery with an
IN predicate, it will take the following form:
SELECT Student.sname, Student.stno FROM Student WHERE "link to Grade_report" ...