Ask the Expert

Subquery to find at least one

I have table called athletics, where in the columns are

student_id varchar(50)
weight_lbs numeric
height_cms numeric
sport varchar(50)

For example

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: