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

Answer: B. 4 rows

A LEFT OUTER JOIN keeps every row of the left table (here the department table); when the right table has no matching row, it produces one row with the right table's columns filled with NULL. Department 10 has two employees, giving two rows; department 20 gives one row; and department 30, which has no employees, gives one row with a NULL name, for a total of four rows. The two employees whose dept_id is NULL match no department and, being rows of the right table, which is not the one kept in full, do not appear in the result. An inner join would also drop the department 30 row, giving three rows. Swapping the tables to employee LEFT OUTER JOIN department keeps all five employees, giving five rows. With outer joins, it is important to check which table keeps all its rows.

• A. This is the count for an inner join. A left outer join also keeps department 30, which has no employees, as one row.
• B. Correct. Two rows for department 10, one for department 20 and one (with a NULL name) for department 30 make four rows.
• C. This is the count with the employee table on the left, keeping all employees.