Employee Bonus
Problem
Employee(empId, name, supervisor, salary) and Bonus(empId, bonus). Return name and bonus where bonus < 1000 or no bonus row exists.
emp=[(3,'B',null,250000),(1,'P',3,10000)] bonus=[(2,500),(7,7000)][('P',null),('B',null)]SELECT e.name, b.bonus
FROM Employee e LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;Explanation
We want every employee whose bonus is under 1000 — including those who have no bonus row at all. The catch is that a plain inner join would silently drop employees who never appear in the Bonus table, which is exactly the group we must keep.
The fix is a LEFT JOIN. It keeps every row from Employee and attaches the matching Bonus row when one exists. When there is no match, the bonus columns come back as NULL instead of vanishing.
The WHERE clause then filters with two cases: b.bonus < 1000 catches small real bonuses, and b.bonus IS NULL catches employees with no bonus row. The IS NULL check is essential because in SQL a comparison like NULL < 1000 is not true — it is unknown — so it would never pass on its own.
Example: employee B (id 3) and employee P (id 1) have no rows in Bonus, so after the left join their bonus is NULL. Both satisfy b.bonus IS NULL, so the result is [('P', null), ('B', null)]. An employee with a bonus of 7000 would be excluded.