Chapter 5: Subqueries: Using Queries Within Other Queries

Qualifying column names

Column names in a statement are implicitly qualified by the table referenced in the from clause at the same level. In the following example, the table name publishers implicitly qualifies the pub_id column in the where clause of the outer query. The reference to pub_id in the select list of the subquery is qualified by the subquery’s from clause—that is, by the titles table:

select pub_name 
from publishers 
where pub_id in 
   (select pub_id 
    from titles 
    where type = "business")

This is what the query looks like with the implicit assumptions spelled out:

select pub_name 
from publishers 
where publishers.pub_id in 
   (select titles.pub_id 
    from titles 
    where type = "business")

It is never wrong to state the table name explicitly, and you can override implicit assumptions about table names by using explicit qualifications.

View this book as PDF