
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How can we create a MySQL view with INNER JOIN?
- How can we convert subqueries to INNER JOIN?
- Difference Between Inner Join and Outer Join in SQL
- How can we write MySQL query for inner joins with the help of keyword JOIN?
- How can we write MySQL query for cross joins with the help of keyword JOIN?
- Difference between Inner and Outer join in SQL
- How to apply CROSS JOIN correctly in MySQL?
- INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?
- 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?
- What is MySQL LEFT JOIN and how can we write MySQL query for it?
- What is MySQL RIGHT JOIN and how can we write MySQL query for it?
- How to do an inner join and outer join of two data frames in R?
- How can we distinguish between MySQL IFNULL() and NULLIF() functions?

Advertisements