How can we distinguish between MySQL CROSS JOIN and INNER JOIN?

We can distinguish between MySQL CROSS JOIN and INNER JOIN only on the basis of join-predicate i.e. the condition specified. While writing the query for INNER JOIN we need to specify the condition but in contrast, we do not need to specify the condition while writing a query for CROSS JOIN. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data −

mysql> Select * from tbl_1;
+----+--------+
| Id | Name |
+----+--------+
| 1  | Gaurav |
| 2  | Rahul  |
| 3  | Raman  |
| 4  | Aarav  |
+----+--------+
4 rows in set (0.00 sec)

mysql> Select * from tbl_2;
+----+---------+
| Id | Name    |
+----+---------+
| A  | Aarav   |
| B  | Mohan   |
| C  | Jai     |
| D  | Harshit |
+----+---------+
4 rows in set (0.00 sec)

Now, the query for INNER JOIN can be as follows −

mysql> SELECT tbl_1.id,tbl_2.id FROM tbl_1 JOIN tbl_2 ON tbl_1.name = tbl_2.name;
+----+----+
| id | id |
+----+----+
| 4  | A  |
+----+----+
1 row in set (0.00 sec)

Now, if we will write the above query without specifying the condition then it would become CROSS JOIN. The query without condition is as follows −

mysql> Select tbl_1.id, tbl_2.id FROM tbl_1 JOIN tbl_2;
+----+----+
| id | id |
+----+----+
| 1  | A  |
| 2  | A  |
| 3  | A  |
| 4  | A  |
| 1  | B  |
| 2  | B  |
| 3  | B  |
| 4  | B  |
| 1  | C  |
| 2  | C  |
| 3  | C  |
| 4  | C  |
| 1  | D  |
| 2  | D  |
| 3  | D  |
| 4  | D  |
+----+----+
16 rows in set (0.00 sec)

Seetha

Published on 20-Feb-2018 11:46:37

• Related Questions & Answers
• Difference Between Inner Join and Outer Join in SQL
• How can we create a MySQL view with INNER JOIN?
• How can we convert subqueries to INNER JOIN?
• INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN?
• Difference between Inner and Outer join in SQL
• INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?
• How to apply CROSS JOIN correctly in MySQL?
• How can we write MySQL query for cross joins with the help of keyword JOIN?
• How can we write MySQL query for inner joins with the help of keyword JOIN?
• How can we distinguish between MySQL IFNULL() and NULLIF() functions?
• How to do an inner join and outer join of two data frames in R?
• How can you perform inner join on two tables using MySQL in Python?
• How can we create a MySQL view with LEFT JOIN?
• How can we create a MySQL view with RIGHT JOIN?
• Python – Cross Join every Kth segment