Interview Corner — Joins and Subqueries

A: INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns all rows from the left table, plus matched rows from the right table. If there is no match, the right side columns are filled with NULL. In QA, LEFT JOIN is critical for finding orphan records — records that exist in one table but have no corresponding record in the related table.

A: CROSS JOIN produces the Cartesian product — every row from table A is paired with every row from table B. If A has 5 rows and B has 3 rows, you get 15 rows. No ON clause is needed. A real use case is generating a test matrix — CROSS JOIN payment_methods with currencies to get every possible combination to test.

A: A self-join is when a table is joined with itself using aliases. The classic example is an employees table where each row has a manager_id that references another row in the same table. You join employees e with employees m ON e.manager_id = m.employee_id to get each employee with their manager's name. Another example: finding duplicate records by joining a table to itself on the potential duplicate column with different primary keys.

A: A regular subquery runs once, produces a result, and the outer query uses it. A correlated subquery references columns from the outer query, so it runs once per row of the outer query. Example: "Find employees earning more than the average salary in their department." The subquery calculates the average for each department (correlated), not the overall average. Correlated subqueries are powerful but can be slow on large datasets.

A: Use LEFT JOIN with IS NULL. For example, to find orders that reference a non-existent customer: LEFT JOIN orders with customers on customer_id, then WHERE customers.customer_id IS NULL. Any rows returned are orphan orders. This is a critical QA check after data migrations, bulk deletes, or when foreign key constraints are not properly enforced.

A: Use a subquery when: (1) you need to compare against an aggregate value like average or max, (2) you only need columns from one table but want to filter based on another, (3) using a JOIN would create duplicates that need DISTINCT. Use a JOIN when: (1) you need columns from multiple tables in the result, (2) performance is critical on large datasets, (3) you need to detect orphan records. Modern database optimizers often convert simple subqueries into JOINs internally.

A: SQL uses three-valued logic: TRUE, FALSE, UNKNOWN. Comparing anything to NULL yields UNKNOWN. When a NOT IN list contains NULL, every comparison becomes NOT (TRUE OR UNKNOWN OR ...) which evaluates to UNKNOWN, so no rows are returned. Fix: add WHERE column IS NOT NULL inside the subquery, or use NOT EXISTS instead. NOT EXISTS is NULL-safe because it only checks for the existence of rows, not values.