Right join `users` and `orders` to show all orders, even if the user has been deleted.

Problem Statement

<p>Right join <code>users</code> and <code>orders</code> to show all <code>orders</code>, even if the user has been deleted.</p>

Examples

Input: users table: +----+-------+ | id | name | +----+-------+ | 1 | Alice | +----+-------+ orders table: +-----+---------+-------+ | id | user_id | total | +-----+---------+-------+ | 101 | 1 | 50 | | 102 | 2 | 75 | | 103 | 1 | 30 | +-----+---------+-------+

Output: +-------+-------+ | name | total | +-------+-------+ | Alice | 50 | | null | 75 | | Alice | 30 | +-------+-------+

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 RIGHT 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 ... RIGHT JOIN ... ON ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Right Join", we query the relational database engine using declarative SQL. The goal is to right join `users` and `orders` to show all orders, even if the user has been deleted. 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 users.name, orders.total FROM users RIGHT JOIN orders ON users.id = orders.user_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.

Right Join

Medium

Right join users and orders to show all orders, even if the user has been deleted.

Example Scenarios

1Example 1

Input:

users table

id	name
1	Alice

orders table

id	user_id	total
101	1	50
102	2	75
103	1	30

Output:

name	total
Alice	50
null	75
Alice	30

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.