极客教程MySQL 如何正确地应用CROSS JOIN
MySQL中的CROSS JOIN返回所联接表中行的笛卡尔积。 让我们通过创建一个表来看一个例子 –
mysql> create table PairDemo
-> (
-> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> UserName varchar(20),
-> UserRelationshipName varchar(20)
-> );
Query OK, 0 rows affected (0.56 sec)
阅读更多：MySQL 教程
例子
使用insert命令在表中插入一些记录。查询如下所示 –
mysql> insert into PairDemo(UserName,UserRelationshipName) values('John','James');
Query OK, 1 row affected (0.15 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','James');
Query OK, 1 row affected (0.20 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','David');
Query OK, 1 row affected (0.25 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Sam','David');
Query OK, 1 row affected (0.19 sec)
使用select语句从表中显示所有记录。查询如下所示 –
mysql> select *from PairDemo;
输出
+--------+----------+----------------------+
| UserId | UserName | UserRelationshipName |
+--------+----------+----------------------+
| 1 | John | James |
| 2 | Carol | James |
| 3 | Carol | David |
| 4 | Sam | David |
+--------+----------+----------------------+
4 rows in set (0.00 sec)
以下是应用CROSS JOIN的查询 –
mysql> SELECT U.UserName, UR.UserRelationshipName
-> FROM (
-> (SELECT DISTINCT UserName from PairDemo) U
-> CROSS JOIN
-> (SELECT DISTINCT UserRelationshipName from PairDemo) UR
-> ) WHERE NOT EXISTS (
-> SELECT * FROM PairDemo tbl
-> WHERE tbl.UserName = U.UserName
-> AND tbl.UserRelationshipName = UR.UserRelationshipName
-> ) ;
这里是实现CROSS JOIN的上述查询的输出 –
+----------+----------------------+
| UserName | UserRelationshipName |
+----------+----------------------+
| Sam | James |
| John | David |
+----------+----------------------+
2 rows in set (0.03 sec)