Learning SQL on SQL Server 2005

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.

Tip

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" ...