How to use MySQL JOIN without ON condition?

---

We can use ‘cross join’ without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on.

Example of cross join

Creating the first table

mysql> CREATE table ForeignTableDemo
   - > (
   - > Id int,
   - > Name varchar(100),
   - > FK int
   - > );
Query OK, 0 rows affected (0.47 sec)

Creating the second table

mysql> CREATE table PrimaryTableDemo
   - > (
   - >  FK int,
   - > Address varchar(100),
   - > primary key(FK)
   - > );
Query OK, 0 rows affected (0.47 sec)

Adding constraints

mysql> ALTER table ForeignTableDemo add constraint FKConst foreign key(FK) references PrimaryTableDemo(FK);
Query OK, 0 rows affected (1.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

To add records into the second table.

mysql> INSERT into PrimaryTableDemo values(1,'US');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT into PrimaryTableDemo values(2,'UK');
Query OK, 1 row affected (0.14 sec)

mysql>  INSERT into PrimaryTableDemo values(3,'Unknown');
Query OK, 1 row affected (0.08 sec)

Displaying records of the second table.

mysql> SELECT * from PrimaryTableDemo;

The following is the output of the above query.

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

To add records into the first table.

mysql>  INSERT into ForeignTableDemo values (1,'John',1);
Query OK, 1 row affected (0.20 sec)

mysql>  INSERT into ForeignTableDemo values (2,'Bob',2);
Query OK, 1 row affected (0.27 sec)

Displaying records using the SELECT command.

mysql> SELECT * from ForeignTableDemo;

The following is the output.

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

We have made a foreign key constraint for all the tables. Now, to use ‘cross join’ without ON, let us see the syntax.

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

Here 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)

• Related Questions & Answers
• How to use Straight Join in MySQL?
• How to use SELF JOIN in MySQL?
• Use JOIN to select record with more than one condition using AND?
• How to use if/else condition in select in MySQL?
• Perform MySQL LEFT JOIN on two tables?
• How to use “OR” condition in MySQL CASE expression?
• How to use count with CASE condition in a MySQL query?
• How to Join Several Partitions on Linux
• How to delete last record (on condition) from a table in MySQL?
• MySQL query to update different fields based on a condition?
• How can we use MySQL SELECT without FROM clause?
• SUM a column based on a condition in MySQL
• ORDER BY records in MySQL based on a condition
• How to use MySQL DISTINCT clause on multiple columns?
• How to apply CROSS JOIN correctly in MySQL?