CROSS JOIN

A CROSS JOIN pairs every row from one table with every row from another, producing every possible combination.

About 5–10 minutes · no sign-up needed

Example

Creating Product-Store Planning Matrices with CROSS JOIN

SELECT p.product_id, p.brand, p.category, s.store_id, s.region, s.channel
FROM products p
CROSS JOIN store_sales s

Result:

product_id	brand	category	store_id	region	channel
P001	NovaTech	Electronics	S01	North	Online
P001	NovaTech	Electronics	S02	South	In-Store
P001	NovaTech	Electronics	S03	West	Online
P002	UrbanWear	Clothing	S01	North	Online
P002	UrbanWear	Clothing	S02	South	In-Store
P002	UrbanWear	Clothing	S03	West	Online

After SELECT, list the column names you want, separated by commas.

SELECT p.product_id, p.brand, p.category, s.store_id, s.region, s.channel

FROM tells SQL which table to pull the data from.

SELECT p.product_id, p.brand, p.category, s.store_id, s.region, s.channel
FROM products p

CROSS JOIN returns every combination of rows from both tables.

SELECT p.product_id, p.brand, p.category, s.store_id, s.region, s.channel
FROM products p
CROSS JOIN store_sales s;

Try each one in your head first, then open the answer.

1. Write a query that returns every combination of rows from orders and customers.

SELECT * FROM orders CROSS JOIN customers

2. Write a CROSS JOIN between orders and returns to produce every possible combination of order and return rows.

SELECT * FROM orders CROSS JOIN returns

3. Write a CROSS JOIN that pairs every product with every review, returning all combinations.

SELECT * FROM products CROSS JOIN reviews

4. Write a CROSS JOIN between orders and shipments to generate every combination of order and shipment rows.

SELECT * FROM orders CROSS JOIN shipments

5. Write a CROSS JOIN between suppliers and supplier_products to produce every possible supplier-product combination.

SELECT * FROM suppliers CROSS JOIN supplier_products

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed