How to apply CROSS JOIN correctly in MySQL?
The CROSS JOIN in MySQL returns the Cartesian product of rows from the joined tables. Let us see an example by creating a table −
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)
Example
Insert some records in the table using insert command. The query is as follows −
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)Display all records from the table using a select statement. The query is as follows −
mysql> select *from PairDemo;
Output
+--------+----------+----------------------+
| UserId | UserName | UserRelationshipName |
+--------+----------+----------------------+
| 1 | John | James |
| 2 | Carol | James |
| 3 | Carol | David |
| 4 | Sam | David |
+--------+----------+----------------------+
4 rows in set (0.00 sec)
The following is the query to apply 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
-> ) ;
Here is the output that displays the result of the above query implementing CROSS JOIN −
+----------+----------------------+
| UserName | UserRelationshipName |
+----------+----------------------+
| Sam | James |
| John | David |
+----------+----------------------+
2 rows in set (0.03 sec)
Published on 19-Mar-2019 08:08:55
