Join `users`, `orders`, and `products` to show user name, order id, and product name.

Problem Statement

<p>Join <code>users</code>, <code>orders</code>, and <code>products</code> to show user name, order id, and product name.</p>

Examples

Input: users table: +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Bob | +----+-------+ orders table: +-----+---------+------------+ | id | user_id | product_id | +-----+---------+------------+ | 101 | 1 | 10 | | 102 | 2 | 20 | | 103 | 1 | 20 | +-----+---------+------------+ products table: +----+--------+ | id | name | +----+--------+ | 10 | Laptop | | 20 | Phone | +----+--------+

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 "Multi-Table Join (3 Tables)", we query the relational database engine using declarative SQL. The goal is to join `users`, `orders`, and `products` to show user name, order id, and product name. 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 u.name AS user_name, o.id AS order_id, p.name AS product_name FROM orders o JOIN users u ON o.user_id = u.id JOIN products p ON o.product_id = p.id;

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.

Multi-Table Join (3 Tables)

Medium

Join users, orders, and products to show user name, order id, and product name.

Example Scenarios

1Example 1

Input:

users table

id	name
1	Alice
2	Bob

orders table

id	user_id	product_id
101	1	10
102	2	20
103	1	20

products table

id	name
10	Laptop
20	Phone

Output:

user_name	order_id	product_name
Alice	101	Laptop
Bob	102	Phone
Alice	103	Phone

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.