NPV Queries - Solution & Explanation

Problem Overview: The database contains two tables: NPV(id, year, npv) and Queries(id, year). For every query pair (id, year), return the corresponding NPV value. If the pair does not exist in the NPV table, return 0 instead.

Approach 1: LEFT JOIN with IFNULL (O(n) time, O(1) extra space)

The most direct solution uses a LEFT JOIN between Queries and NPV on both id and year. A left join guarantees every row from Queries appears in the result even when no matching record exists in NPV. When a match is missing, SQL returns NULL for the npv column. Use IFNULL(npv, 0) (or COALESCE) to replace that NULL value with 0. The database performs the join once across the two tables, giving linear performance relative to the number of rows scanned. This approach is concise, efficient, and the typical pattern used for handling missing relational data.

This technique relies on core concepts from SQL and database querying. Specifically, you match rows using a composite join condition Queries.id = NPV.id AND Queries.year = NPV.year. The result set directly produces the required columns: the query identifiers plus the computed NPV value.

Approach 2: Correlated Subquery with COALESCE (O(n) time, O(1) extra space)

Another option retrieves the NPV value using a correlated subquery. For each row in Queries, run a subquery that searches the NPV table for the matching (id, year). If the subquery returns NULL or no result, wrap it with COALESCE(..., 0) to return 0. This approach is logically straightforward because it mirrors the problem statement: for each query, look up the NPV value.

While the complexity is still roughly linear for small datasets due to database optimization, correlated subqueries can be less efficient on large tables compared to joins. Most query planners internally transform this pattern into a join anyway. Still, understanding both forms is useful when working with SQL joins and relational lookups.

Recommended for interviews: The LEFT JOIN approach is the expected solution. It shows you understand relational joins and how to handle missing matches using IFNULL or COALESCE. The subquery version demonstrates equivalent logic but is less commonly preferred because joins scale better and are easier to optimize.