I just screwed up the world's easiest interview question so I guess that merits a blog posting.

What is the difference between outer and inner joins? My answer: The main table to which another table is joined may return numerous rows for the "joined-in" data for every row it returns in an outer join, but in an inner join there is a strict one-to-one relationship.

 
 

Wrong. This is not so. In both cases the main table to which another table is joined may return numerous rows for the "joined-in" data for every row it returns, however it's best, and perhaps a little counterintuitive, to think of the "joined-in" table as in the driver's seat. All of the records will be returned from the joined table except for those for which there is not a match in the main table, and if the join happens on a foreign key restraint relationship where a child is joined up to a parent then forget the "except" part of what I say altogether leaving: All of the records will be returned from the joined table. So what is the difference between outer and inner joins? If the main table has records which do not have a dance partner in the joined table these records will appear in outer joins and will not appear in inner joins. In these records the columns that come back for the joined table data points in the record set the query coughs up will just have null values.

 
 

Addendum 10/31/2016: What I say above about only one record per record for the joined table above really isn't true, certainly not if you join a parent to a child. The thing to really remember is that an inner join shows just the records where both tables have data while a left join will also show the main table's data even if there is not corresponding data in the joined table for a row returned.