MYSQL CROSS JOIN

The CROSS JOIN will return the records which will be the number of rows in the first table multiplied by the number of rows in the second table.

Assume the table employees with the following data.

empno name age department_id location salary emp_type_id
1 Andrew 30 4 India 100000 2
2 Beslin 29 3 India 90000 1

Then the `employeetype' with the following data

emp_type_id emp_type
1 Full-Time
2 Contract

Syntax for CROSS JOIN

SELECT column_name(s)
FROM table_name_1
CROSS JOIN table_name_2;

Example

select employees.name,employees.location,employeetype.emp_type from employees 
cross join employeetype;

Output

name location emp_type
Andrew India Full-Time
Andrew India Contract
Beslin India Full-Time
Beslin India Contract

The CROSS JOIN will return all the records of both the tables irrespective of other table matches.
Query with CROSS JOIN is a costly query since it return huge volume of records.


Most Read