Question 1

INNER JOIN basics

Accepted Answer

INNER JOIN returns only rows where the join condition matches in both tables. Carol has NULL dept_id which matches no department id, so she is excluded. HR (id 30) has no matching employee, so it is also excluded. Only Alice and Bob have matching dept_ids.

Question 2

LEFT JOIN keeps all left rows

Accepted Answer

LEFT JOIN keeps every row from the left table (employees) regardless of whether a match exists in the right table. Alice matches Sales. Bob has no dept_id, so no department row matches - the dept column is NULL in his result row. Use LEFT JOIN when you need all left-table rows even if the right side has no data.

Question 3

RIGHT JOIN keeps all right rows

Accepted Answer

RIGHT JOIN keeps every row from the right table (departments) regardless of whether a match exists on the left. Sales matches Alice. Eng has no matching employee, so e.name is NULL in that row. RIGHT JOIN is less common in practice - most developers rewrite it as a LEFT JOIN by swapping table order.

Question 4

What INNER JOIN excludes

Accepted Answer

INNER JOIN only keeps rows where a match exists on both sides. When 5 and 4 produce 3, that means some rows from each table were dropped for having no counterpart. There is nothing wrong with the query - unmatched rows are simply excluded by design. If you need to preserve unmatched rows, use a LEFT, RIGHT, or FULL OUTER JOIN instead.