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

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

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

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

Hint

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

Practice

1. What does a CROSS JOIN do in SQL?

easy

A. It returns all possible combinations of rows from two tables.

B. It returns only matching rows based on a condition.

C. It deletes rows from both tables.

D. It updates rows in one table based on another.

Solution

1. Step 1: Understand the definition of CROSS JOIN

   A CROSS JOIN combines every row from the first table with every row from the second table, creating all possible pairs.

2. Step 2: Compare with other join types

   Unlike INNER JOIN or LEFT JOIN, CROSS JOIN does not require a matching condition and returns the Cartesian product.

3. Final Answer:

   It returns all possible combinations of rows from two tables. -> Option A

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: CROSS JOIN = multiply rows from both tables [OK]

Common Mistakes:

• Confusing CROSS JOIN with INNER JOIN
• Thinking CROSS JOIN filters rows
• Assuming CROSS JOIN deletes or updates data

2. Which of the following is the correct syntax to perform a CROSS JOIN between tables Employees and Departments?

easy

A. SELECT * FROM Employees INNER JOIN Departments;

B. SELECT * FROM Employees JOIN Departments ON Employees.id = Departments.id;

C. SELECT * FROM Employees CROSS JOIN Departments;

D. SELECT * FROM Employees WHERE CROSS JOIN Departments;

Solution

1. Step 1: Identify correct CROSS JOIN syntax

   The correct syntax uses the keyword CROSS JOIN between two table names without any ON condition.

2. Step 2: Eliminate incorrect options

   SELECT * FROM Employees JOIN Departments ON Employees.id = Departments.id; uses JOIN with ON condition (not CROSS JOIN). SELECT * FROM Employees INNER JOIN Departments; uses INNER JOIN without ON, which is invalid. SELECT * FROM Employees WHERE CROSS JOIN Departments; uses WHERE incorrectly.

3. Final Answer:

   SELECT * FROM Employees CROSS JOIN Departments; -> Option C

4. Quick Check:

   Correct CROSS JOIN syntax = SELECT * FROM Employees CROSS JOIN Departments; [OK]

Hint: CROSS JOIN uses keyword CROSS JOIN without ON clause [OK]

Common Mistakes:

• Using JOIN without ON for CROSS JOIN
• Trying to use WHERE for joining tables
• Confusing INNER JOIN with CROSS JOIN syntax

3. Given two tables:
Colors with rows: Red, Blue
Shapes with rows: Circle, Square, Triangle
What will be the result of:
SELECT Colors.color, Shapes.shape FROM Colors CROSS JOIN Shapes;

medium

A. [('Red', 'Circle'), ('Red', 'Square'), ('Red', 'Triangle'), ('Blue', 'Circle'), ('Blue', 'Square'), ('Blue', 'Triangle')]

B. [('Red', 'Circle'), ('Blue', 'Square'), ('Red', 'Triangle')]

C. [('Red', 'Circle'), ('Blue', 'Circle'), ('Red', 'Square'), ('Blue', 'Square')]

D. Empty result set

Solution

1. Step 1: Count rows in each table

   Colors has 2 rows, Shapes has 3 rows.

2. Step 2: Calculate CROSS JOIN result

   CROSS JOIN returns all pairs: 2 * 3 = 6 rows combining each color with each shape.

3. Final Answer:

   [('Red', 'Circle'), ('Red', 'Square'), ('Red', 'Triangle'), ('Blue', 'Circle'), ('Blue', 'Square'), ('Blue', 'Triangle')] -> Option A

4. Quick Check:

   2 colors * 3 shapes = 6 pairs [OK]

Hint: Multiply row counts for CROSS JOIN result size [OK]

Common Mistakes:

• Listing only matching pairs instead of all combinations
• Confusing CROSS JOIN with INNER JOIN
• Ignoring total number of rows in result

4. Consider this SQL query:
SELECT * FROM A CROSS JOIN B WHERE A.id = B.id;
What is the main issue with this query?

medium

A. It will cause a syntax error because CROSS JOIN cannot have WHERE clause.

B. It updates tables A and B unintentionally.

C. It returns no rows because CROSS JOIN filters all rows.

D. It produces the same result as INNER JOIN but less efficiently.

Solution

1. Step 1: Understand CROSS JOIN with WHERE filter

   CROSS JOIN creates all pairs, then WHERE filters pairs where A.id = B.id.

2. Step 2: Compare with INNER JOIN

   This is equivalent to INNER JOIN on A.id = B.id but less efficient because CROSS JOIN generates many unnecessary pairs first.

3. Final Answer:

   It produces the same result as INNER JOIN but less efficiently. -> Option D

4. Quick Check:

   CROSS JOIN + WHERE = INNER JOIN behavior [OK]

Hint: CROSS JOIN + WHERE = INNER JOIN but slower [OK]

Common Mistakes:

• Thinking WHERE is invalid with CROSS JOIN
• Assuming CROSS JOIN filters rows automatically
• Confusing SELECT with UPDATE statements

5. You have two tables:
Products with 4 rows and Colors with 5 rows.
You want to generate a list of all product-color combinations but exclude combinations where the product is discontinued.
Which query correctly uses CROSS JOIN and filtering?

hard

A. SELECT p.name, c.color FROM Products p INNER JOIN Colors c ON p.discontinued = FALSE;

B. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c WHERE p.discontinued = FALSE;

C. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c ON p.discontinued = FALSE;

D. SELECT p.name, c.color FROM Products p, Colors c WHERE p.discontinued = TRUE;

Solution

1. Step 1: Use CROSS JOIN to get all combinations

   Use CROSS JOIN between Products and Colors to get every product-color pair.

2. Step 2: Filter out discontinued products

   Apply WHERE clause to keep only products where discontinued = FALSE.

3. Step 3: Check other options for errors

   SELECT p.name, c.color FROM Products p INNER JOIN Colors c ON p.discontinued = FALSE; uses INNER JOIN incorrectly with ON condition unrelated to join keys. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c ON p.discontinued = FALSE; uses ON with CROSS JOIN, which is invalid syntax. SELECT p.name, c.color FROM Products p, Colors c WHERE p.discontinued = TRUE; filters discontinued = TRUE, opposite of requirement.

4. Final Answer:

   SELECT p.name, c.color FROM Products p CROSS JOIN Colors c WHERE p.discontinued = FALSE; -> Option B

5. Quick Check:

   CROSS JOIN + WHERE filters correctly [OK]

Hint: Filter after CROSS JOIN with WHERE clause [OK]

Common Mistakes:

• Using ON clause with CROSS JOIN
• Filtering with wrong discontinued value
• Confusing INNER JOIN with CROSS JOIN usage