如何在MySQL中正确应用CROSS JOIN？
MySQL中的CROSSJOIN从联接表返回行的笛卡尔积。让我们通过创建表来查看示例-
mysql> create table PairDemo
-> (
-> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> UserName varchar(20),
-> UserRelationshipName varchar(20)
-> );示例
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into PairDemo(UserName,UserRelationshipName) values('John','James');
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','James');
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','David');
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Sam','David');使用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)
以下是应用CROSSJOIN的查询-
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
-> ) ;这是显示上述查询实现CROSSJOIN的结果的输出-
+----------+----------------------+ | UserName | UserRelationshipName | +----------+----------------------+ | Sam | James | | John | David | +----------+----------------------+ 2 rows in set (0.03 sec)