できるPRO MySQL できるPROシリーズ Kindle版の内容をまとめる。
CROSS JOIN
)SELECT * FROM テーブル1, テーブル2;
SELECT * FROM テーブル1 CROSS JOIN テーブル2;
テーブル名.列名
CREATE TABLE num_a( i INTEGER);
CREATE TABLE num_b( j INTEGER);
MariaDB [dekirusample2]> CREATE TABLE num_a( i INTEGER);CREATE TABLE num_b( j INTEGER);
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.08 sec)
INSERT INTO num_a VALUES (10), (11), (12);
INSERT INTO num_b VALUES (2), (3);
MariaDB [dekirusample2]> INSERT INTO num_a VALUES (10), (11), (12);INSERT INTO num_b VALUES (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
SELECT * FROM num_a;
SELECT * FROM num_b;
MariaDB [dekirusample2]> SELECT * FROM num_a;SELECT * FROM num_b;
+------+
| i |
+------+
| 10 |
| 11 |
| 12 |
+------+
3 rows in set (0.00 sec)
+------+
| j |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec)
CROSS JOIN
で組み合わせを表示SELECT * FROM num_a CROSS JOIN num_b;
MariaDB [dekirusample2]> SELECT * FROM num_a CROSS JOIN num_b;
+------+------+
| i | j |
+------+------+
| 10 | 2 |
| 10 | 3 |
| 11 | 2 |
| 11 | 3 |
| 12 | 2 |
| 12 | 3 |
+------+------+
6 rows in set (0.01 sec)
SELECT * FROM num_a, num_b;
MariaDB [dekirusample2]> SELECT * FROM num_a, num_b;
+------+------+
| i | j |
+------+------+
| 10 | 2 |
| 10 | 3 |
| 11 | 2 |
| 11 | 3 |
| 12 | 2 |
| 12 | 3 |
+------+------+
6 rows in set (0.00 sec)
SELECT * FROM num_a, num_b
WHERE MOD(num_a.i, num_b.j) = 0;
MariaDB [dekirusample2]> SELECT * FROM num_a, num_b WHERE MOD(num_a.i, num_b.j) = 0;
+------+------+
| i | j |
+------+------+
| 10 | 2 |
| 12 | 2 |
| 12 | 3 |
+------+------+
3 rows in set (0.02 sec)