SQL Interview: Joins, Keys and Constraints

Joins

Q. What is the difference between INNER JOIN and LEFT JOIN?

An inner join returns only rows with a match on both sides. A left join returns every row from the left table, filling the right side with NULL where there is no match. Follow up they will ask: which would you use to list departments with zero employees? The left join - the inner join drops them.

Q. What is the difference between WHERE and ON in an outer join?

ON decides which rows match; WHERE filters the result after the join. A condition on the optional table in WHERE removes the NULL extended rows and silently turns the outer join into an inner one.

-- Inner join in disguise
FROM departments d LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.status = 'active'

-- Genuinely outer
FROM departments d LEFT JOIN employees e
     ON e.dept_id = d.id AND e.status = 'active'

Q. What is a self join and when do you need one?

A table joined to itself with two aliases, used when rows relate to other rows in the same table - employee to manager, category to parent category. Use a LEFT JOIN so the root row, with no parent, is not dropped.

Q. What is a CROSS JOIN?

Every row of one table paired with every row of the other, with no condition - m x n rows. Deliberately useful for generating a complete grid; accidentally produced by forgetting a join condition.

Q. How do you find rows in table A with no match in table B?

-- NOT EXISTS: NULL safe, usually best
SELECT d.name FROM departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

-- LEFT JOIN ... IS NULL: test the right table's KEY, not a nullable column
SELECT d.name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE  e.id IS NULL;

Q. Why can a join return more rows than the table you started with?

Because joining to the many side of a one to many relationship duplicates the parent row once per child. That is why SUM on a parent column across such a join is inflated - and why the fix is to aggregate the child first, not to add DISTINCT.

Keys

Q. Primary key versus unique key?

Q. Candidate key, alternate key, composite key, super key?

• Super key - any column set that is unique, extras allowed.
• Candidate key - a minimal super key.
• Primary key - the candidate key you chose.
• Alternate key - the candidate keys you did not choose.
• Composite key - any key of more than one column.

Q. Natural or surrogate key - which and why?

Surrogate as the primary key, because it is narrow and never changes; natural keys enforced with UNIQUE, because they are real business rules. A natural primary key that can change forces an update to every referencing row.

Q. Does a foreign key prevent NULL?

No. A foreign key constrains non NULL values only; NULL means "no reference" and cannot dangle. Add NOT NULL if the relationship is mandatory.

Constraints

Q. Name the constraint types and what each guarantees.

PRIMARY KEY - unique and not null; FOREIGN KEY - the reference exists; UNIQUE - no duplicate values; NOT NULL - a value is present; CHECK - a boolean condition holds; DEFAULT - a value when the column is omitted.

Q. Does CHECK (salary > 0) prevent a NULL salary?

No - and this is a favourite question. A CHECK passes when the condition is TRUE or UNKNOWN, and NULL > 0 is UNKNOWN. You need NOT NULL as well.

Q. What are ON DELETE CASCADE, RESTRICT and SET NULL?

What happens to child rows when the parent is deleted: CASCADE deletes them, RESTRICT refuses the delete, SET NULL clears the child's foreign key. Choose by asking whether the child still means anything without the parent.

Q. DELETE, TRUNCATE and DROP?

Questions to have an answer ready for

1. Write a query listing every department with its employee count, including empty departments.
2. Explain why a LEFT JOIN with a WHERE filter on the right table behaves as an inner join.
3. Given orders and order_items, why is SUM(orders.total) wrong after joining them?
4. When would you choose a composite primary key over a surrogate id?