Re:Our SQL interview questions
by aidos on Tuesday May 28, 2013 @10:21AM
(#5780193)
Fair point. Experience has taught me to internally question whether or not each relationship should use a left join or not. The fact that it says "List all departments" made me think it should be a left join. In fact, to me that screams "left join".
Really, in a philosophical sort of way, it's the use of the query that determines which join to use. They want ALL departments, you give them ALL departments. Using a left join you protect yourself in the future - with a performance hit. As you say, if there was a guarantee that the data didn't contain empty departments you'd use an inner join.
Most importantly, a reasonable developer should know to ask the question - if not of the examiner, at least to themselves. Just making an assumption is not the right approach.
Really, in a philosophical sort of way, it's the use of the query that determines which join to use. They want ALL departments, you give them ALL departments. Using a left join you protect yourself in the future - with a performance hit. As you say, if there was a guarantee that the data didn't contain empty departments you'd use an inner join.
Most importantly, a reasonable developer should know to ask the question - if not of the examiner, at least to themselves. Just making an assumption is not the right approach.