An employee table (emp_no, name, dept_id) has four rows whose dept_id values are, in order, 10, 20, 10 and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT employee.name, department.dept_name FROM employee INNER JOIN department ON employee.dept_id = department.dept_id return?

Answer: A. 3 rows

An INNER JOIN returns only the combinations of rows that satisfy the ON condition. The two employees with dept_id 10 combine with department 10, and the one employee with dept_id 20 combines with department 20, so the result has three rows. The employee whose dept_id is NULL is not joined to any department, because a comparison with NULL is never true, and disappears from the result. Department 30, which has no employees, does not appear either. Four rows is the count for a left outer join that keeps every row of the employee table (employee LEFT OUTER JOIN department). Twelve rows is the CROSS JOIN (Cartesian product), which forms every combination without a condition: 4 × 3 = 12 rows.

• A. Correct. Only the three pairs with matching dept_id are returned.
• B. This is the count for a left outer join that keeps all employees. The employee with a NULL dept_id disappears in an inner join.
• C. This is the number of rows of the Cartesian product, every combination without a join condition (4 × 3).