CODEWITHSUNDEEP
sql
user3381431
user3381431

Reputation: 93

SQL sub query syntax

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)

Upvotes: 0

Views: 350

Answers (3)

ant_iw3r
ant_iw3r

Reputation: 222

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.

Upvotes: 1

Ima Miri
Ima Miri

Reputation: 967

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

Upvotes: 0

Deddy H
Deddy H

Reputation: 352

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

Upvotes: 0

Related Questions