CROSS JOIN behavior in PostgreSQL - Mini Project: Build & Apply

Understanding CROSS JOIN Behavior in PostgreSQL

📖 Scenario: You are working with two small tables in a store database: one lists products and the other lists colors. You want to see every possible combination of product and color to plan your inventory.

🎯 Goal: Build a query using CROSS JOIN to generate all combinations of products and colors.

📋 What You'll Learn

Create a table called products with columns id (integer) and name (text) and insert exactly these rows: (1, 'Shirt'), (2, 'Pants')

Create a table called colors with columns id (integer) and color (text) and insert exactly these rows: (1, 'Red'), (2, 'Blue')

Write a SELECT query that uses CROSS JOIN between products and colors

Select products.name and colors.color in the result

💡 Why This Matters

🌍 Real World

Retailers often need to see all possible combinations of product attributes to plan inventory and marketing.

💼 Career

Understanding <code>CROSS JOIN</code> helps in data analysis and reporting tasks where combinations of data sets are required.

1

Create the products table and insert data

Create a table called products with columns id as integer and name as text. Insert these exact rows: (1, 'Shirt') and (2, 'Pants').

PostgreSQL

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

Need a hint?

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

2

Create the colors table and insert data

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

PostgreSQL

CREATE TABLE products (id INTEGER, name TEXT);
INSERT INTO products (id, name) VALUES (1, 'Shirt'), (2, 'Pants');
-- Create the colors table and insert rows
-- Your code here

Need a hint?

Similar to step 1, use CREATE TABLE and INSERT INTO for the colors table.

3

Write a CROSS JOIN query to combine products and colors

Write a SELECT query that selects products.name and colors.color from products CROSS JOIN colors.

PostgreSQL

CREATE TABLE products (id INTEGER, name TEXT);
INSERT INTO products (id, name) VALUES (1, 'Shirt'), (2, 'Pants');
CREATE TABLE colors (id INTEGER, color TEXT);
INSERT INTO colors (id, color) VALUES (1, 'Red'), (2, 'Blue');

-- Write the CROSS JOIN query below
-- Your code here

Need a hint?

Use SELECT with CROSS JOIN to get all combinations.

4

Complete the query with an alias for clarity

Modify the CROSS JOIN query to add table aliases p for products and c for colors. Select p.name and c.color.

PostgreSQL

CREATE TABLE products (id INTEGER, name TEXT);
INSERT INTO products (id, name) VALUES (1, 'Shirt'), (2, 'Pants');
CREATE TABLE colors (id INTEGER, color TEXT);
INSERT INTO colors (id, color) VALUES (1, 'Red'), (2, 'Blue');

-- Add aliases p and c to products and colors
-- Your code here

Need a hint?

Use AS or just put the alias after the table name for clarity.