MySQL CROSS JOIN

MySQL CROSS JOIN combines all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. It links several columns from the multiple tables in any order. This join can link any column of the first table with another table. The "cross join" links two tables with each row. It is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables.

Syntax

The "cross join" syntax shows below.

SELECT * FROM table1 CROSS JOIN table2; 

The "cross join" with id syntax shows below.

SELECT * FROM table1.id CROSS JOIN table2.id; 

Examples of the MySQL cross join

1) Example:

Execute the below query to join two tables. Two tables join required columns using specified conditions. Here, the query using the "ON" condition with cross join.

mysql> select d.department_name, d.department_id, 
s.subject_name, s.subject_id
FROM department d
CROSS JOIN subject s
ON d.department_id = s.subject_id;

OUTPUT

The above image shows combined columns of the two tables using cross join. The "department_name" and "department_id" columns display in the department table. The "subject_name" and "students" columns display in the subject table.

2) Example:

Execute the below query to join two tables. Two tables join required columns using specified conditions. Here, the query selects the entire columns of the table.

mysql> select *
FROM department d
CROSS JOIN subject s
ON d.department_id = s.subject_id;

OUTPUT

The above image shows the entire columns of the department and student tables because of the cross join.

3) Example:

Execute the below query to join two tables. Two tables join all columns using the "CROSS JOIN" query. This query uses the ON and WHERE clause with CROSS JOIN.

mysql> select d.department_name, d.department_id, s.subject_name, s.subject_id
FROM department d
CROSS JOIN subject s
ON d.department_id = s.subject_id 
WHERE subject_id < 3;

OUTPUT

The above image returns the required columns and rows of the two tables using cross join. It uses clauses and conditions for two tables in a single query. Here, we can see four columns and two rows.

4) Example:

Execute the below query to join two tables with the required columns. This query uses the "ON" and the "ORDER BY" clause with CROSS JOIN.

mysql> select d.department_name, d.department_id, s.subject_name, s.subject_id
FROM department d
CROSS JOIN subject s
ON d.department_id = s.subject_id 
ORDER BY 
department_id DESC,
subject_id DESC;

OUTPUT

The above output image shows the given columns of the two tables using cross join. Here, you can show four columns in descending order of data.

5) Example:

Execute the below cross join query to join two tables. Two tables join required columns using specified conditions. For example, this query uses "WHERE" with "AND" conditions.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
CROSS JOIN subject s
WHERE d.department_name = "Bachelor of Technology"
AND  s.subject_name = "Electronics";

OUTPUT

The above image shows the required columns of the two tables. The cross query works with the logical condition.

6) Example:

Execute the below cross join query to join two tables. Two tables join required columns using specified conditions. For example, this query uses "WHERE" with "OR" conditions.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
CROSS JOIN subject s
WHERE d.department_name = "Bachelor of Technology"
OR  s.subject_name = "Electronics";

OUTPUT

The above image shows the columns of the two tables. This query shows the required columns of the department table and student table. The cross query works with the logical condition. Both conditions fulfill using the "OR" logical condition.

7) Example: the cross join with the "USING" clause example shows below.

Execute the below query to join two tables. For example, the "CROSS JOIN" query uses "USING" clause with the "subject_id" foreign key.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> CROSS JOIN subject s
    -> USING (subject_id);

OUTPUT

The above output table displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key. The above table works on the cross join query with the "USING" clause.

8) Example: the cross joins with the "GROUP BY" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "CROSS JOIN" query uses "GROUP BY" clause with the "subject_id" foreign key. It also uses AND logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> CROSS JOIN subject s
    -> USING (subject_id)
    -> WHERE d.department_name = " Bachelor of Engineering"
    -> GROUP BY subject_id;

OUTPUT

The above output image displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key.

9) Example: the cross join with the multiple clause example shows below.

Execute the below query to join two tables. This example uses "ORDER BY" and "using" clauses with the "subject_id" foreign key. It also uses OR logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> CROSS JOIN subject s
    -> USING (subject_id)
    -> WHERE d.department_name = "Bachelor of Technology"
    -> OR  s.subject_name = "Computer Science"
    -> ORDER BY department_id DESC, subject_id DESC;

OUTPUT

The above output table displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key. The above table works on the cross join query with the "USING" clause. Here, the table applies a cross join query with the "ORDER BY" clause. The table data display in descending order.