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

初めてのSQL
Alan Beaulieu (著)

開発環境

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

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

10-4.

入出力結果(Terminal)

$ mysql -u lrngsql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
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 (
    -> 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.31 sec)

mysql> quit;
Bye
$

おまけ: 文を作成したpythonのコード、入出力結果(対話型コマンドライン)

$ python
Python 3.2.3 (default, Apr 18 2012, 20:17:30) 
[GCC 4.2.1 Compatible Apple Clang 3.0 (tags/Apple/clang-211.12)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> for x in range(10):
...     print("select " + str(x) + " num union all")
... 
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 union all
>>> for x in range(10):
...     print("select " + str(x*10) + " num union all")
... 
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 union all
>>> quit()
$