SQL sub query syntax

Question

I am going through an online tutorial here and am having difficulty understanding their solutions for Q6 to Q8. The link with the answers is below:

http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial?answer=1

Essentially I am having difficulty understanding/picturing what the highlighted statement does. world x and world y are exactly the same tables. Any guidance would be appreciated.

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent)

ant_iw3r · Accepted Answer · 2014-03-05 17:36:11Z

1

Essentially this is giving you the name of the row with the largest area for each continent.

Line 1 is your final result set from world x.

Line 2 is filtering your final result set to the row with the largest area.

Line3/4 is your subquery which is being used for the comparison. This subquery will be executed for each row in the dataset.

Because the where clause of the subquery is ensuring that the continents are equal each row is only compared to rows from the same continent.

So for each row in x it is compared to each row in y where the continents match. If the row is >= every row in y it ends up in your final result set.

(this is to help understanding and not necessarily exactly how the work is performed by the sql engine)

For each row in the primary query the subquery is run and returns all rows that match the continent of the current row in the primary query. Comparison is made for the primary row to each subquery row and the final result set is populated with only the row from the primary where area is >= to all of the rows in the subquery. It then moves on to the second row of the primary query until all comparisons have been made and then returns the final result set to you.

edited Mar 5, 2014 at 17:36

answered Mar 5, 2014 at 1:24

ant_iw3r

2221 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3381431 Over a year ago

Thanks this kind of explains it. I have difficulty picturing the set of rows that result from the subquery (SELECT area FROM world y WHERE y.continent=x.continent). This is still a bit confusing to me.

ant_iw3r Over a year ago

Maybe this will help. (this is to help understanding and not necessarily exactly how the work is performed by the sql engine) For each row in the primary query the subquery is run and returns all rows that match the continent of the current row in the primary query. Comparison is made for the primary row to each subquery row and the final result set is populated with only the row from the primary where area is >= to all of the rows in the subquery. It then moves on to the second row of the primary query until all comparisons have been made and then returns the final result set to you.

Deddy H · Accepted Answer · 2014-03-05 01:24:14Z

0

How about this one :

SELECT continent, name, area 
FROM world x
INNER JOIN world y
 ON y.continent=x.continent
WHERE x.area >= ALL

i think inner join more easy to understand

answered Mar 5, 2014 at 1:24

Deddy H

3522 gold badges7 silver badges19 bronze badges

Comments

Ima Miri · Accepted Answer · 2014-03-05 01:24:46Z

0

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database.

you can read more here

answered Mar 5, 2014 at 1:24

Ima Miri

9972 gold badges15 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL sub query syntax

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related