Q

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?


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close