CROSS JOIN cartesian product in SQL - Mini Project: Build & Apply

Understanding CROSS JOIN Cartesian Product in SQL

📖 Scenario: You are working in a small online store database. You want to understand how to combine every product with every available color option to see all possible product-color combinations.

🎯 Goal: Build a SQL query using CROSS JOIN to create a cartesian product of two tables: Products and Colors.

📋 What You'll Learn

Create a table called Products with columns ProductID and ProductName and insert three specific products.

Create a table called Colors with columns ColorID and ColorName and insert three specific colors.

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

Select ProductName and ColorName in the final query.

💡 Why This Matters

🌍 Real World

Online stores often need to show all possible combinations of products and options like colors or sizes. CROSS JOIN helps generate these combinations.

💼 Career

Understanding CROSS JOIN is important for database querying and reporting tasks in data analysis, software development, and business intelligence roles.

1

Create the Products table and insert data

Write SQL statements to create a table called Products with columns ProductID (integer) and ProductName (text). Then insert these three rows exactly: (1, 'T-Shirt'), (2, 'Jeans'), and (3, 'Sneakers').

SQL

-- Create Products table and insert data
-- Your code here

Need a hint?

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

2

Create the Colors table and insert data

Write SQL statements to create a table called Colors with columns ColorID (integer) and ColorName (text). Then insert these three rows exactly: (1, 'Red'), (2, 'Blue'), and (3, 'Green').

SQL

CREATE TABLE Products (
  ProductID INT,
  ProductName TEXT
);

INSERT INTO Products (ProductID, ProductName) VALUES
  (1, 'T-Shirt'),
  (2, 'Jeans'),
  (3, 'Sneakers');

-- Create 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 SQL query that selects ProductName and ColorName from the Products table cross joined with the Colors table using CROSS JOIN.

SQL

CREATE TABLE Products (
  ProductID INT,
  ProductName TEXT
);

INSERT INTO Products (ProductID, ProductName) VALUES
  (1, 'T-Shirt'),
  (2, 'Jeans'),
  (3, 'Sneakers');

CREATE TABLE Colors (
  ColorID INT,
  ColorName TEXT
);

INSERT INTO Colors (ColorID, ColorName) VALUES
  (1, 'Red'),
  (2, 'Blue'),
  (3, 'Green');

-- Write CROSS JOIN query to select ProductName and ColorName
-- Your code here

Need a hint?

Use SELECT with CROSS JOIN to combine all rows from both tables.

4

Complete the query with an alias for clarity

Modify the previous CROSS JOIN query by adding table aliases p for Products and c for Colors. Select p.ProductName and c.ColorName using these aliases.

SQL

CREATE TABLE Products (
  ProductID INT,
  ProductName TEXT
);

INSERT INTO Products (ProductID, ProductName) VALUES
  (1, 'T-Shirt'),
  (2, 'Jeans'),
  (3, 'Sneakers');

CREATE TABLE Colors (
  ColorID INT,
  ColorName TEXT
);

INSERT INTO Colors (ColorID, ColorName) VALUES
  (1, 'Red'),
  (2, 'Blue'),
  (3, 'Green');

-- Add aliases p and c to Products and Colors
-- Select p.ProductName and c.ColorName
-- Your code here

Need a hint?

Use table aliases after the table names and prefix column names with these aliases.