
- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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?
Advertisements