I have table called athletics, where in the columns are
student_id varchar(50) weight_lbs numeric height_cms numeric sport varchar(50)
c001 189 190 soccer c002 175 180 football c003 210 195 football c004 186 175 baseball
I want to find all athletes that are not football players but have weight_lbs greater than at least one football player. How do you find this using subqueries?
What you want is a correlated subquery that selects all the football players that have a weight less than the non-football athlete you're looking at ("looking at" is sort of a technical term I use to describe how a correlated subquery works) and if there are any, then the athlete you're looking at is selected --
select * from athletics X where sport <> 'football' and exists ( select 1 from athletics where sport = 'football' and weight_lbs < X.weight_lbs )
The X is the correlation variable that ties the weight comparison in the subquery to the particular row being examined by the outer query. The subquery selects 1 because it doesn't matter what it selects; what matters is whether any row satisfies the subbquery condition, and therefore makes the EXISTS condition of the outer query true. I'm not really sure what is most efficient -- select 1, select *, or select null (see How does WHERE EXISTS ( SELECT NULL... ) work?). I suppose a good optimizer will ignore the select list in the subquery anyway.
This was first published in May 2002