← Salesforce Interview Insights

Salesforce·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

SQL-heavy technical screen for a full-stack role at Salesforce. Just one question but they really wanted to dig into the reasoning behind the approach, not just get a working query.

Questions Asked (1)

Q1

Given a users table and an orders table, write a SQL query returning all users who have never placed an order (user_id and name, ordered by user_id). Then walk through the trade-offs between LEFT JOIN with a NULL check, NOT EXISTS, and NOT IN.

Technical Trade-offsData Modeling
Author's notes

I went with LEFT JOIN first since that's what I reach for by default, and the query itself was fine.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by writing a correct SQL 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. Emphasize that NOT IN is unsafe with NULLs, while NOT EXISTS and LEFT JOIN are generally preferred, with NOT EXISTS often being the most robust and performant.

Pro tip: Mention that NOT IN can silently return no rows if the subquery contains NULLs, and that LEFT JOIN with a NULL check on the right table's primary key is a common pattern but can be less efficient than NOT EXISTS on large datasets. Also, note that NOT EXISTS is often optimized to an anti-join, which is efficient.

1. Write the query

Provide a correct SQL query using one of the methods, e.g., LEFT JOIN with NULL check, ensuring you select user_id and name and order by user_id.

2. Explain LEFT JOIN with NULL check

Describe how it works: left join users to orders, then filter where orders.user_id IS NULL. Mention it's intuitive but may be less efficient if the join produces many rows.

3. Explain NOT EXISTS

Describe how it works: correlated subquery checking for absence of orders per user. Highlight that it handles NULLs correctly and often performs well as an anti-join.

4. Explain NOT IN

Describe how it works: filter users whose user_id is not in the set of user_ids from orders. Warn about NULL pitfalls: if orders.user_id contains NULL, the entire NOT IN returns no rows.

5. Compare trade-offs

Summarize: correctness (NOT EXISTS and LEFT JOIN safe, NOT IN unsafe with NULLs), performance (NOT EXISTS often best, LEFT JOIN can be okay with proper indexes, NOT IN can be slow and risky), readability (LEFT JOIN intuitive, NOT EXISTS clear, NOT IN concise but dangerous).

Key Points to Mention

  • NULL handling: NOT IN returns no rows if subquery has NULLs; NOT EXISTS and LEFT JOIN are safe.
  • Performance: NOT EXISTS often optimized as anti-join; LEFT JOIN may require DISTINCT if multiple orders per user; NOT IN can be slow due to subquery evaluation.
  • Indexing: Ensure indexes on orders.user_id for efficient joins and subqueries.
  • Readability and maintainability: LEFT JOIN is intuitive but can be verbose; NOT EXISTS is clear and safe; NOT IN is concise but error-prone.
  • Semantic differences: NOT EXISTS and NOT IN are logically equivalent only when NULLs are absent; LEFT JOIN with NULL check is equivalent when join condition is on user_id.
  • Database-specific optimizations: Some databases optimize NOT IN to anti-join if no NULLs, but it's not guaranteed.

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