CROSS JOIN in MySQL - Mini Project: Build & Apply

Understanding CROSS JOIN in MySQL

📖 Scenario: You are managing a small online store. You want to create a list of all possible combinations of products and colors available for your customers.

🎯 Goal: Build a MySQL query using CROSS JOIN to generate every combination of products and colors.

📋 What You'll Learn

Create two tables: products and colors with specified data

Write a CROSS JOIN query to combine all products with all colors

Select the product name and color name in the result

💡 Why This Matters

🌍 Real World

Online stores often need to show all possible combinations of product attributes like color and size to customers.

💼 Career

Understanding CROSS JOIN helps in data analysis and generating comprehensive reports combining multiple data sets.

1

Create the products table and insert data

Create a table called products with columns id (integer) and name (varchar). Insert these exact rows: (1, 'T-shirt'), (2, 'Jeans').

MySQL

-- Create the products table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Create the colors table and insert data

Create a table called colors with columns id (integer) and color_name (varchar). Insert these exact rows: (1, 'Red'), (2, 'Blue').

MySQL

-- Create the colors table and insert data
-- Your code here

Need a hint?

Similar to step 1, create the colors table and insert the given rows.

3

Write a CROSS JOIN query to combine products and colors

Write a SELECT query that uses CROSS JOIN to combine products and colors. Select products.name as product and colors.color_name as color.

MySQL

-- Write a CROSS JOIN query to get all product and color combinations
-- Your code here

Need a hint?

Use FROM products CROSS JOIN colors to get all combinations.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the query to sort the results by product ascending and then by color ascending.

MySQL

-- Add ORDER BY product and color ascending
-- Your code here

Need a hint?

Use ORDER BY product ASC, color ASC to sort the output.