MySQL CROSS JOIN
Combining every row from two tables
🔗 What is CROSS JOIN?
CROSS JOIN returns the Cartesian product of two tables, combining every row from the first table with every row from the second table. It creates all possible combinations between tables.
-- Simple CROSS JOIN example
SELECT * FROM colors
CROSS JOIN sizes;
Output:
| color | size |
|---|---|
| Red | Small |
| Red | Large |
| Blue | Small |
| Blue | Large |
Key CROSS JOIN Concepts
Cartesian Product
Creates all possible combinations
SELECT * FROM table1
CROSS JOIN table2;No Condition
No ON or WHERE clause needed
SELECT a.name, b.name
FROM products a, categories b;Result Size
Rows = Table1 rows × Table2 rows
-- 3 rows × 4 rows = 12 rows
SELECT * FROM t1 CROSS JOIN t2;Use Carefully
Can produce very large result sets
-- Add WHERE to filter results
WHERE condition;🔹 Basic CROSS JOIN Syntax
CROSS JOIN combines every row from the first table with every row from the second table. This operation is useful when you need to generate all possible combinations between two datasets, such as creating product variations or scheduling scenarios.
-- Basic CROSS JOIN syntax
SELECT column1, column2
FROM table1
CROSS JOIN table2;
-- Alternative syntax (implicit)
SELECT column1, column2
FROM table1, table2;Example Result:
Every row from table1 paired with every row from table2
🔹 Practical CROSS JOIN Example
Let's create a product catalog with all possible combinations of colors and sizes. This demonstrates how CROSS JOIN helps generate comprehensive product listings by matching each color with every available size, creating a complete inventory matrix.
-- Sample tables
CREATE TABLE colors (
color_name VARCHAR(50)
);
CREATE TABLE sizes (
size_name VARCHAR(50)
);
-- Insert sample data
INSERT INTO colors VALUES ('Red'), ('Blue'), ('Green');
INSERT INTO sizes VALUES ('Small'), ('Medium'), ('Large');
-- CROSS JOIN to get all combinations
SELECT
colors.color_name,
sizes.size_name,
CONCAT(color_name, ' - ', size_name) AS product_variant
FROM colors
CROSS JOIN sizes
ORDER BY color_name, size_name;Output:
| color_name | size_name | product_variant |
|---|---|---|
| Blue | Large | Blue - Large |
| Blue | Medium | Blue - Medium |
| Blue | Small | Blue - Small |
| Green | Large | Green - Large |
| ... | ... | 9 total rows |
🔹 CROSS JOIN with WHERE Clause
You can filter CROSS JOIN results using WHERE clause to limit the combinations. This is particularly useful when you want all possible pairings but only need specific matches based on certain criteria, reducing the result set to meaningful data.
-- CROSS JOIN with filtering
SELECT
e.employee_name,
d.department_name
FROM employees e
CROSS JOIN departments d
WHERE d.location = 'New York'
AND e.status = 'Active';Output:
Only active employees paired with New York departments
🔹 Real-World Use Cases
CROSS JOIN is valuable for generating test data, creating scheduling matrices, and building product catalogs. Common applications include appointment scheduling where you need all time slots paired with all service providers, or inventory systems requiring every product matched with all warehouse locations.
-- Use Case 1: Generate date ranges with hours
SELECT
dates.date_value,
hours.hour_value,
CONCAT(date_value, ' ', hour_value, ':00') AS time_slot
FROM date_table dates
CROSS JOIN hour_table hours
WHERE dates.date_value BETWEEN '2024-01-01' AND '2024-01-07';
-- Use Case 2: Product pricing matrix
SELECT
p.product_name,
r.region_name,
p.base_price * r.price_multiplier AS regional_price
FROM products p
CROSS JOIN regions r;