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)

Samual Sam

Updated on 26-Jun-2020 10:16:28

• Related Questions & Answers
• How can we distinguish between MySQL CROSS JOIN and INNER JOIN?
• Python – Cross Join every Kth segment
• How to use % wildcard correctly in MySQL?
• How to correctly enclose subquery in MySQL?
• HTML5 appcache with Safari causing cross-site css to not load correctly
• How can we write MySQL query for cross joins with the help of keyword JOIN?
• How to correctly use WITH ROLLUP in MySQL?
• How to correctly implement conditions in MySQL stored procedure?
• How to use Straight Join in MySQL?
• How to use SELF JOIN in MySQL?
• How to correctly use DELIMITER in a MySQL stored procedure?
• How to apply NOW() to timestamps field in MySQL Workbench?
• Correctly implement the AND condition in MySQL
• How to use MySQL JOIN without ON condition?
• MySQL join two tables?