Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.