屑俺日記

☆ 自己非等結合(self-non-equi-join)

対戦相手一覧、のような用途があるらしい。

sqlite> .schema teams
CREATE TABLE teams (id integer primary key, name text);
 
sqlite> select * from teams;
id    name         
----  -------------
1     Team A       
2     Team B       
3     Team C  

sqlite> select t.id, t.name ,e.id, e.name from teams t 
   ...> join teams e on t.id != e.id;
id    name           id    name
----  -------------  ----  ----
1     Team A         2     Team B
1     Team A         3     Team C
2     Team B         1     Team A
2     Team B         3     Team C
3     Team C         1     Team A
3     Team C         2     Team B

☆ しばらく放置していたら

mysql> use $DATABASE;
No connection. Trying to reconnect...
Connection id:    60
Current database: *** NONE ***
 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

一応自動で動き始めたようだったけど。

☆ UNIONとUNION ALL

mysql> SELECT 1 番号, 'ひとつ' 文字
    -> UNION
    -> SELECT 1 番号, 'ひとつ' 文字;
+--------+-----------+
| 番号   | 文字      |
+--------+-----------+
|      1 | ひとつ    |
+--------+-----------+
1 row in set (0.00 sec)
 
mysql> SELECT 1 番号, 'ひとつ' 文字
    -> UNION ALL
    -> SELECT 1 番号, 'ひとつ' 文字;
+--------+-----------+
| 番号   | 文字      |
+--------+-----------+
|      1 | ひとつ    |
|      1 | ひとつ    |
+--------+-----------+
2 rows in set (0.00 sec)

☆ intersect と except

「共通部分」と「差」といったところか。

sqlite> select * from teams2;
id    name         
----  -------------
2     Team B       
3     Team C       
5     Team E 

sqlite> select * from teams 
   ...> intersect
   ...> select * from teams2;
id    name         
----  -------------
2     Team B       
3     Team C  

sqlite> select * from teams
   ...> except
   ...> select * from teams2;
id    name         
----  -------------
1     Team A    

teams と teams2を入れ替えれば Team E が出る。
sqlite3には except all はない。

☆ ないならないで

なんかMySQL5.5には、どちらもないっぽい。
JOINなどを使うことになりそう。

mysql> select * from teams1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | second |
|    3 | third  |
|    4 | fourth |
+------+--------+
3 rows in set (0.00 sec)
 
mysql> select * from teams2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | first  |
|    2 | second |
|    3 | third  |
+------+--------+
3 rows in set (0.00 sec)

mysql> select t1.id, t1.name from teams1 t1  
    -> join teams2 t2 on t1.id = t2.id;
+------+--------+
| id   | name   |
+------+--------+
|    2 | second |
|    3 | third  |
+------+--------+
2 rows in set (0.00 sec)

exceptのかわり(片方にしかないデータの炙り出し)はどうしたものか。
ちなみにPostgresにはintersectもexceptもあった。