CROSS JOIN in MySQL

Introduction

A CROSS JOIN combines every row from one table with every row from another table. It helps to see all possible pairs between two sets of data.

When you want to pair each product with every store to check availability.

To create all possible combinations of colors and sizes for a clothing line.

When you need to generate test data by combining two lists.

To match every employee with every project for brainstorming assignments.

Syntax

MySQL

SELECT * FROM table1 CROSS JOIN table2;

CROSS JOIN returns the Cartesian product of the two tables.

If table1 has M rows and table2 has N rows, the result will have M x N rows.

Examples

This pairs every fruit with every color.

MySQL

SELECT * FROM fruits CROSS JOIN colors;

Shows all possible employee and project pairs.

MySQL

SELECT employees.name, projects.title FROM employees CROSS JOIN projects;

Sample Program

This example creates two small tables and uses CROSS JOIN to combine every fruit with every color.

MySQL

CREATE TABLE fruits (name VARCHAR(10));
INSERT INTO fruits VALUES ('Apple'), ('Banana');
CREATE TABLE colors (color VARCHAR(10));
INSERT INTO colors VALUES ('Red'), ('Yellow');

SELECT fruits.name, colors.color FROM fruits CROSS JOIN colors;

Important Notes

CROSS JOIN can produce very large results if tables have many rows, so use it carefully.

It does not require any condition to join tables.

If you want to limit results, use WHERE or LIMIT clauses after CROSS JOIN.

Summary

CROSS JOIN pairs every row from the first table with every row from the second.

It creates all possible combinations between two tables.

Useful for generating combinations or test data.