8.1. 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"
       IN  ("link to Student" - subquery involving Grade_report)

In this format, the part of the query that contains:

    SELECT Student.sname, Student.stno
    FROM   Student
    WHERE  "link to Grade_report"

is said to be the outer query. The part of the query that contains:

    ("link to Student" - subquery involving Grade_report)

is the inner query.

The link between the Student table and the Grade_report table is the student number. In the Student table, the appropriate column is stno, and in the Grade_report table, it is student_number. When linking the tables in the subquery with an IN predicate, the linking columns are all that can be mentioned in the WHERE..IN and in the result set of the subquery. Thus, the statement with a subquery is as follows:

    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

The part of the query before the IN is often called the outer query. The part of the query after the IN is called the inner query.

This query produces the following output (31 rows):

    sname                stno
    -------------------- -----
    Lineas               2
    Mary                 3
    Zelda                5
    Ken                  6
    Mario                7
    Brenda               8
    Richard              10
    Kelly                13
    Lujack               14
    Reva                 15
    Harley               19
    Donald               20
    Chris                24
    Lynette              34
    Susan                49
    Hillary              121
    Phoebe               122
    Holly                123
    Sadie                125
    Jessica              126
    Steve                127
    Cedric               129
    George               132
    Jerry                142
    Cramer               143
    Fraiser              144
    Francis              146
    Smithly              147
    Sebastian            148
    Lindsay              155
    Stephanie            157

    (31 row(s) affected)