Difference between revisions of "Using nested SELECT/ja"

From SQLZOO
Jump to: navigation, search
(SELECT の中で SELECT を使う)
(SELECT の中で SELECT を使う)
Line 6: Line 6:
 
     <b>SELECT continent FROM world WHERE name = 'Brazil'</b>
 
     <b>SELECT continent FROM world WHERE name = 'Brazil'</b>
  
     の実行結果は<code>'South America'</code> で、この値をブラジル'Brazil'と同じ大陸に有る全ての国名のリストを得るために利用する</p>
+
     の実行結果は<code>'South America'</code> で、この値をブラジル'Brazil'と同じ大陸にある全ての国名のリストを得るために利用する</p>
  
 
<div class='qu'>
 
<div class='qu'>

Revision as of 14:49, 18 April 2018

元のチュートリアル SELECT within SELECT Tutorial/ja に戻る。

SELECT の中で SELECT を使う

導出テーブル(SELECTの実行結果としてのテーブル)の使い方は SELECT FROM SELECT を見る 導出テーブル.

SELECT 文の実行結果は、他のSELECT文の中で 値やテーブルとして利用できる。 次の例文: SELECT continent FROM world WHERE name = 'Brazil' の実行結果は'South America' で、この値をブラジル'Brazil'と同じ大陸にある全ての国名のリストを得るために利用する

ブラジル'Brazil' と同じ大陸にある各国のリストを求める

SELECT name FROM world WHERE continent = 
(SELECT continent 
FROM world WHERE name = 'Brazil')

Alias

Some versions of SQL insist that you give the subquery an alias. Simply put AS somename after the closing bracket:

SELECT name FROM world WHERE continent = 
  (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent

Multiple Results

The subquery may return more than one result - if this happens the query above will fail as you are testing one value against more than one value. It is safer to use IN to cope with this possibility.

The phrase (SELECT continent FROM world WHERE name = 'Brazil' OR name='Mexico') will return two values ('North America' and 'South America'). You should use:

SELECT name, continent FROM world
WHERE continent IN
 (SELECT continent FROM world WHERE name='Brazil'
                                 OR name='Mexico')

List each country and its continent in the same continent as 'Brazil' or 'Mexico'.

SELECT name, continent FROM world
WHERE continent IN
  (SELECT continent 
     FROM world WHERE name='Brazil'
                   OR name='Mexico')

Subquery on the SELECT line

If you are certain that only one value will be returned you can use that query on the SELECT line.

Show the population of China as a multiple of the population of the United Kingdom

SELECT
 population/(SELECT population FROM world
             WHERE name='United Kingdom')
  FROM world
WHERE name = 'China'

Operators over a set

These operators are binary - they normally take two parameters:

=     equals
>     greater than
<     less than
>=    greater or equal
<=    less or equal

You can use the words ALL or ANY where the right side of the operator might have multiple values.

Show each country that has a population greater than the population of ALL countries in Europe.

Note that we mean greater than every single country in Europe; not the combined population of Europe.

SELECT name FROM world
 WHERE population > ALL
      (SELECT population FROM world
        WHERE continent='Europe')

Return to the SELECT within SELECT Tutorial