Interview Q&A / SQL & DBMS

1. How do you find rows in one table that have no matching row in another?

   Use LEFT JOIN and keep rows where the right table's key IS NULL, or use NOT EXISTS. Both are safer than NOT IN, which fails when the subquery returns a NULL.

   IntermediateQuick answerCoding1 min read
2. What is a FULL OUTER JOIN and how can you simulate it in MySQL?

   FULL OUTER JOIN returns all rows from both tables, matching where possible and filling NULLs where not. MySQL has no FULL JOIN, so combine a LEFT JOIN and a RIGHT JOIN with UNION.

   IntermediateQuick answer1 min read
3. What is a NATURAL JOIN and why do many developers avoid it?

   NATURAL JOIN automatically joins on all columns with the same name in both tables. It is risky because adding or renaming a column silently changes the join. Explicit ON or USING is safer.

   IntermediateQuick answer1 min read
4. What is the difference between putting a filter in ON versus WHERE in a LEFT JOIN?

   A filter on the right table in ON limits which rows match while still keeping every left row. The same filter in WHERE runs after the join and removes the NULL-extended rows, turning the LEFT JOIN into an INNER JOIN.

   IntermediateQuick answer1 min read