Salesforce·Software Engineer·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

Apr 2026

Summary

Salesforce full-stack screen, pretty SQL-heavy. One question but they really dug into the edges of it, not just 'write the query and move on'.

Questions Asked (1)

Given a users table and a transactions table, write a SQL query to find all users who have never made a transaction. Then walk through the trade-offs between LEFT JOIN with a NULL check, NOT EXISTS, and NOT IN, including why NOT IN can behave unexpectedly with NULLs.

Technical Trade-offsData Modeling

Author's notes

Got the LEFT JOIN version out fast, felt good about it.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by writing a correct query using one of the three methods, then systematically compare LEFT JOIN with NULL check, NOT EXISTS, and NOT IN in terms of correctness, performance, and readability. Highlight the NULL pitfall of NOT IN and recommend NOT EXISTS or LEFT JOIN as safer alternatives.

Pro tip: Mention that NOT IN can silently return no rows if the subquery returns any NULL, and that NOT EXISTS is generally the safest and often the most performant choice. Also, note that LEFT JOIN with a NULL check can be efficient if the join column is indexed, but may produce duplicate rows if the join is not unique.

1. Write a correct query

Choose one method (e.g., LEFT JOIN) and write a syntactically correct SQL query that returns users with no transactions. Ensure you handle potential NULLs appropriately.

2. Explain LEFT JOIN with NULL check

Describe how LEFT JOIN returns all users and matches transactions; filtering WHERE transactions.id IS NULL gives users with no transactions. Mention performance considerations and potential duplicate rows if the join is not unique.

3. Explain NOT EXISTS

Describe how NOT EXISTS uses a correlated subquery to check for the absence of related rows. Highlight its safety with NULLs and typical performance benefits when proper indexes exist.

4. Explain NOT IN and its NULL pitfall

Describe how NOT IN compares a value against a list; if the subquery returns any NULL, the entire NOT IN condition evaluates to UNKNOWN, resulting in no rows returned. Emphasize that this is a common and subtle bug.

5. Compare trade-offs and recommend

Summarize trade-offs: correctness (NOT EXISTS and LEFT JOIN are safe; NOT IN is unsafe with NULLs), performance (NOT EXISTS often optimized well; LEFT JOIN can be efficient with indexes; NOT IN may be slower), and readability. Recommend NOT EXISTS or LEFT JOIN as best practices.

Key Points to Mention

NOT IN returns no rows if the subquery returns any NULL, due to three-valued logic (UNKNOWN).
NOT EXISTS is NULL-safe and often performs well, especially with indexes on the join column.
LEFT JOIN with IS NULL check is intuitive but can produce duplicates if the join is not unique; use DISTINCT or ensure uniqueness.
Performance depends on indexes, data distribution, and the database optimizer; NOT EXISTS and LEFT JOIN are generally preferred over NOT IN.
In SQL, NULL represents unknown, so comparisons with NULL yield UNKNOWN, not TRUE or FALSE.
Salesforce context: consider data skew and large datasets; NOT EXISTS may be more scalable in some cases.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.