Generate all possible combinations of `colors` and `sizes`.

Problem Statement

<p>Generate all possible combinations of <code>colors</code> and <code>sizes</code>.</p>

Examples

Input: colors table: +-------+ | color | +-------+ | Red | | Blue | +-------+ sizes table: +------+ | size | +------+ | S | | M | | L | +------+

Output: +-------+------+ | color | size | +-------+------+ | Red | S | | Blue | S | | Red | M | | Blue | M | | Red | L | | Blue | L | +-------+------+

Explanation: The query joins the matching records on the related keys and projects the requested fields.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Determine which tables contain the required columns and relate them using INNER JOIN on the corresponding foreign/primary keys. 💡 Hint 2: Write out the ON condition matching key fields (e.g. ON a.key = b.key) and apply any filtering in the WHERE clause. 💡 Hint 3: Select only the requested columns in the SELECT clause with clear aliases if needed: SELECT ... FROM ... INNER JOIN ... ON ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Cross Join", we query the relational database engine using declarative SQL. The goal is to generate all possible combinations of `colors` and `sizes`. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply table joins to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT color, size FROM colors CROSS JOIN sizes;

Complexity Analysis

Time Complexity O(N * M) worst-case, O(N + M) with hash/merge join on indexed keys.

Space Complexity O(N) for join buffer and result set.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Cross Join

Medium

Generate all possible combinations of colors and sizes.

Example Scenarios

1Example 1

Input:

colors table

color
Red
Blue

sizes table

size
S
M
L

Output:

color	size
Red	S
Blue	S
Red	M
Blue	M
Red	L
Blue	L

Explanation:

The query joins the matching records on the related keys and projects the requested fields.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.