8.3. When the Join Cannot Be Turned into a Subquery

When a column from a table needs to be in the result set, that table has to be in the outer query. If two tables are being used, and if columns from both tables have to be in the result set, a join is necessary. This type of join cannot be turned into a subquery, because information from both tables has to be in the result set. But if the result set does not need the columns from more than one table, then the join can be turned into a subquery. The other tables can be included such that the filtering conditions can be in the subquery (or inner query), and the table that has the needed result set columns is in the outer query.

Consider this example. Our original query (the first query discussed in this chapter), requested the list of names and student numbers of students who made As or Bs in any course. Student names and numbers are both in the Student table; the Grade_report table is needed only as a filter, so we could write this as a subquery, and also turn it into a join.

Now, if this original query had asked for output from the Grade_report table also, such as, "list the names, numbers, and grades of all students who have made As or Bs," the query would be asking for information from both the Student and Grade_report tables. In this case, you would have to join the two tables to get the information; you could not just query the Grade_report table, because that table has no names in it. Similarly, the Student table contains no grades. So you would not be able to write this as a subquery. Refer again to the original query example:

    SELECT Student.sname, Student.stno
    FROM   Student
    WHERE  Student.stno
      IN (SELECT   gr.student_number
          FROM     Grade_report gr
          WHERE    gr.grade = 'B' OR  gr.grade = 'A')
    ORDER BY Student.stno

This query asks for information only from the Student table (student names and numbers). Although the query used the Grade_report table, nothing from the Grade_report table was in the outer result set. Again, the Grade_report table is needed only as a filter (to get the student numbers of those who have As and Bs); hence we were able to write this as a subquery.

The following join query asks for information from both the Student and Grade_report tables (a result set that lists both names and grades of all students who have made As or Bs in any course):

    SELECT DISTINCT Student.sname, gr.grade
    FROM   Student, Grade_report gr
    WHERE  Student.stno = gr.student_number
    AND       (gr.grade = 'B' OR gr.grade = 'A')

This query produces 41 rows of output (of which we show the first 25 rows here):

    sname                grade
    -------------------- -----
    Brenda               A
    Brenda               B
    Cedric               A
    Cedric               B
    Chris                B
    Cramer               B
    Donald               A
    Fraiser              B
    Francis              B
    George               B
    Harley               B
    Hillary              B
    Holly                A
    Holly                B
    Jerry                A
    Jessica              A
    Jessica              B
    Kelly                B
    Ken                  B
    Lindsay              B
    Lineas               A
    Lineas               B
    Lujack               A
    Lujack               B
    Lynette              A
    .
    .
    .

    (41 row(s) affected)

As this example demonstrates, if information from a table is needed in a result set, then that table cannot be buried in a subqueryit must be in the outer query.