CROSS JOIN
语法说明
CROSS JOIN
用于实现两个表的笛卡尔积,也就是生成两个表中所有行的组合。
语法结构
>SELECT column_list
FROM table1
CROSS JOIN table2;
示例
CREATE TABLE Colors (
color_id INT AUTO_INCREMENT,
color_name VARCHAR(50),
PRIMARY KEY (color_id)
);
CREATE TABLE Fruits (
fruit_id INT AUTO_INCREMENT,
fruit_name VARCHAR(50),
PRIMARY KEY (fruit_id)
);
INSERT INTO Colors (color_name) VALUES ('Red'), ('Green'), ('Blue');
INSERT INTO Fruits (fruit_name) VALUES ('Apple'), ('Banana'), ('Cherry');
mysql> SELECT c.color_name, f.fruit_name FROM Colors c CROSS JOIN Fruits f;--生成一个包含所有颜色和所有水果组合的结果集
+------------+------------+
| color_name | fruit_name |
+------------+------------+
| Red | Apple |
| Green | Apple |
| Blue | Apple |
| Red | Banana |
| Green | Banana |
| Blue | Banana |
| Red | Cherry |
| Green | Cherry |
| Blue | Cherry |
+------------+------------+
9 rows in set (0.00 sec)
mysql> SELECT c.color_name,f.fruit_name FROM Colors c CROSS JOIN Fruits f WHERE c.color_name = 'Red' AND f.fruit_name = 'Apple';--筛选出特定颜色和特定水果的组合
+------------+------------+
| color_name | fruit_name |
+------------+------------+
| Red | Apple |
+------------+------------+
1 row in set (0.01 sec)