Search code examples
sqlsubquery

Alternative for subquery in the FROM clause

I have this query that has subquery in the FROM clause

SELECT x, y 
FROM FOO 
WHERE y IN (SELECT y 
            FROM 
                (SELECT y, z, MIN(v)  
                 FROM BAR 
                 GROUP BY y, z))

The professor teaching the class said that not all databases support it and I should only use subqueries in the WHERE clause.

I'm trying to convert the query above not to use a subquery in the FROM clause but can't figure this out.

Any idea ?

Solution

  • You can do as below -

    Select distinct FOO.x,FOO.y 
  FROM FOO 
  JOIN ( SELECT y,z, min(v)  FROM  BAR groupby y,z) TAB 
  ON FOO.y = TAB.y;

    I agree with the other comment from Gordon that the aggregation is quite unnecessary. However, I have kept close to what you tried in my response and still give the correct results while eliminating the extra sub-query using Join.