SQL queries using joins instead of inner queries
Filed under PayPal
Free read 3 of 3Convert a subquery (such as an IN or EXISTS clause) into an explicit SQL JOIN (INNER JOIN or LEFT JOIN).
The question
Write an SQL query using joins to achieve the same result as an inner query (subquery) implementation.
Constraints
- The query must avoid correlated or nested subqueries.
- Must correctly handle duplicate rows using appropriate join conditions or DISTINCT.
Approach
Convert a subquery (such as an IN or EXISTS clause) into an explicit SQL JOIN (INNER JOIN or LEFT JOIN).
Identify the relationship between the primary table and the subquery table to structure the ON clause correctly.
Instead of SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active'), use SELECT DISTINCT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active'.
Tips
- What they are testing: Verify correctness with null values in joined columns and ensure duplicate records are not incorrectly multiplied.
- Common mistake: Forgetting to use DISTINCT or GROUP BY when a one-to-many join introduces duplicate rows from the parent table.
Related PayPal questions
Collected from public candidate posts and coding forums. Updated Nov 2026.