Explain the main SQL join types and give an example of when you would use each.

What the interviewer wants to hear

• INNER JOIN returns only matching rows from both tables
• LEFT JOIN keeps all rows from the left table, with NULLs where nothing matches
• RIGHT and FULL OUTER JOIN keep unmatched rows from the right or both sides
• CROSS JOIN returns every combination of rows
• Join columns should normally be indexed

Model answer

An INNER JOIN returns only rows that have a match in both tables, for example orders with their customer details. A LEFT JOIN keeps every row from the left table and fills the right side with NULLs when there is no match, so I use it for questions like "all customers and their orders, including customers who never ordered", or with WHERE right.id IS NULL to find customers with no orders. A RIGHT JOIN is the mirror image, and most people just swap the tables and use LEFT. A FULL OUTER JOIN keeps unmatched rows from both sides, which is useful when reconciling two data sources. A CROSS JOIN returns every combination, for example generating all dates for every store. For performance, the join columns, especially foreign keys, should usually be indexed.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?