How to use MySQL JOIN without ON condition?

We can use 'cross join' without any conditions. The cross join results in a Cartesian product. For example, if a table has3records, while the other table has2record, the first record will match all records in the second table. Then, the same process is repeated for the second record, and so on.

Example of a cross join

Create the first table

mysql> CREATE table ForeignTableDemo
　　　-　> (
　　　-　> Id int,
　　　-　> Name varchar(100),
　　　-　> FK int
　　　-　> );

Create the second table

mysql> CREATE table PrimaryTableDemo
　　　-　> (
　　　-　>   FK int,
　　　-　> Address varchar(100),
　　　-　> primary key(FK)
　　　-　> );

Add constraint

mysql> ALTER table ForeignTableDemo add constraint FKConst foreign key(FK) references PrimaryTableDemo(FK);
Records: 0    Duplicates: 0    Warnings: 0

Add records to the second table.

mysql> INSERT into PrimaryTableDemo values (1,'US');
mysql> INSERT into PrimaryTableDemo values (2,'UK');
mysql> INSERT into PrimaryTableDemo values (3,'Unknown');

Display records from the second table.

mysql> SELECT　*　from PrimaryTableDemo;

Here is the output of the above query.

+----+---------+
| FK | Address |
+----+---------+
|　　1　| US                 |
|　　2　| UK                 |
|　　3　| Unknown |
+----+---------+
3　rows in set (0.00 sec)

Add records to the first table.

mysql> INSERT into ForeignTableDemo values (1,'John',1);
mysql> INSERT into ForeignTableDemo values (2,'Bob',2);

Use the SELECT command to display records.

mysql> SELECT　*　from ForeignTableDemo;

Here is the output.

+------+------+------+
| Id | Name | FK |
+------+------+------+
|　　　　1　| John |　　　　1　|
|　　　　2　| Bob |　　　　2　|
+------+------+------+
2　rows in set (0.00 sec)

We have applied foreign key constraints to all tables. Now, let's see the syntax for using 'cross join' without ON.

mysql> SELECT ForeignTableDemo.Id, ForeignTableDemo.Name, PrimaryTableDemo.Address
　　　-　> from ForeignTableDemo
　　　-　> cross join PrimaryTableDemo;

This is the output.

+------+------+---------+
| Id | Name | Address |
+------+------+---------+
|　　　　1　| John | US                 |
|　　　　2　| Bob | US                 |
|　　　　1　| John | UK                 |
|　　　　2　| Bob | UK                 |
|　　　　1　| John | Unknown |
|　　　　2　| Bob | Unknown |
+------+------+---------+
6　rows in set (0.00 sec)