Interview Q&A / SQL & DBMS
SQL & DBMS interview questions
Joins, keys, normalisation, indexes and transactions for placement and backend interviews.
Prefer rapid revision? Practise SQL & DBMS short interview answers →
- What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL when there is no match.
BeginnerIn-depth2 min read - What is a CROSS JOIN and when would you use it?
A CROSS JOIN pairs every row of one table with every row of the other, giving rows1 x rows2 results. It is useful for generating combinations, such as every size with every colour.
BeginnerQuick answer1 min read - What is a self join and when do you need one?
A self join joins a table to itself using two different aliases. It is used when rows relate to other rows in the same table, such as an employee and their manager.
BeginnerQuick answer1 min read - What is the difference between LEFT JOIN and RIGHT JOIN?
LEFT JOIN keeps every row of the left table and fills NULLs where the right has no match. RIGHT JOIN does the opposite. They are mirror images, and any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables.
BeginnerQuick answer1 min read - 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 - 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 - 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 - 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