SQL - 結合(直積(デカルト積、cross join))

初めてのSQL
Alan Beaulieu (著)

開発環境

• OS: OS X Lion - Apple
• データベース言語: SQL
• リレーショナルデータベース: MySQL

『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-8733-181-8) の10章(結合),　10.4(練習問題)10-3を解いてみる。

10-3.

SQL文(TextWrangler)

select ones.num + tens.num + 1
from (
select 0 num union all
select 1 num union all
select 2 num union all
select 3 num union all
select 4 num union all
select 5 num union all
select 6 num union all
select 7 num union all
select 8 num union all
select 9 num
) ones
cross join(
select 0 num union all
select 10 num union all
select 20 num union all
select 30 num union all
select 40 num union all
select 50 num union all
select 60 num union all
select 70 num union all
select 80 num union all
select 90 num) tens;

入出力結果(Terminal)

$ mysql -u lrngsql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select ones.num + tens.num + 1
    -> from (
    -> quit
    -> ;
ERROR 1046 (3D000): No database selected
mysql> select ones.num + tens.num + 1
    -> from (
    -> select 0 num union all
    -> select 1 num union all
    -> select 2 num union all
    -> select 3 num union all
    -> select 4 num union all
    -> select 5 num union all
    -> select 6 num union all
    -> select 7 num union all
    -> select 8 num union all
    -> select 9 num
    -> ) ones
    -> cross join(
    -> select 0 num union all
    -> select 10 num union all
    -> select 20 num union all
    -> select 30 num union all
    -> select 40 num union all
    -> select 50 num union all
    -> select 60 num union all
    -> select 70 num union all
    -> select 80 num union all
    -> select 90 num) tens;
+-------------------------+
| ones.num + tens.num + 1 |
+-------------------------+
|                       1 |
|                       2 |
|                       3 |
|                       4 |
|                       5 |
|                       6 |
|                       7 |
|                       8 |
|                       9 |
|                      10 |
|                      11 |
|                      12 |
|                      13 |
|                      14 |
|                      15 |
|                      16 |
|                      17 |
|                      18 |
|                      19 |
|                      20 |
|                      21 |
|                      22 |
|                      23 |
|                      24 |
|                      25 |
|                      26 |
|                      27 |
|                      28 |
|                      29 |
|                      30 |
|                      31 |
|                      32 |
|                      33 |
|                      34 |
|                      35 |
|                      36 |
|                      37 |
|                      38 |
|                      39 |
|                      40 |
|                      41 |
|                      42 |
|                      43 |
|                      44 |
|                      45 |
|                      46 |
|                      47 |
|                      48 |
|                      49 |
|                      50 |
|                      51 |
|                      52 |
|                      53 |
|                      54 |
|                      55 |
|                      56 |
|                      57 |
|                      58 |
|                      59 |
|                      60 |
|                      61 |
|                      62 |
|                      63 |
|                      64 |
|                      65 |
|                      66 |
|                      67 |
|                      68 |
|                      69 |
|                      70 |
|                      71 |
|                      72 |
|                      73 |
|                      74 |
|                      75 |
|                      76 |
|                      77 |
|                      78 |
|                      79 |
|                      80 |
|                      81 |
|                      82 |
|                      83 |
|                      84 |
|                      85 |
|                      86 |
|                      87 |
|                      88 |
|                      89 |
|                      90 |
|                      91 |
|                      92 |
|                      93 |
|                      94 |
|                      95 |
|                      96 |
|                      97 |
|                      98 |
|                      99 |
|                     100 |
+-------------------------+
100 rows in set (0.10 sec)

mysql> quit
Bye
$