I've been writing SQL queries for almost 19 years now. At this point I've done more right outer joins in job interviews than I've actually needed to do in real life. Why is this such a common interview question?

Well...

LEFT (OUTER) JOIN
<-> RIGHT (OUTER) JOIN

LEFT and RIGHT join are the same operation - except the table join order.

And this is the reason why this question is asked many times....

People who learned SQL by heart often don't understand that most statements in SQL are superfluous - as they are mathematical expressions which can be transformed.

If I ask 'What is a RIGHT OUTER JOIN?' I love it when people say: LEFT JOIN with swapped arguments... Because most people do not 'grok' that SQL is interpreted and that eg. the query that you wrote is completely different from the query the server executed....

It's a trap. Most Join types do not even exist in common database systems.

@IntrusionCM by any chance do you have some resource/reference about that?

I got curious about your explanation and I would like to read more about it

@0x412E2047696C

Look eg here:
https://dev.mysql.com/doc/refman/...

But most database systems behave this way.

In a simple (not fully correct way): An optimizer is designed to optimize by operation (e.g. DeMorgans Law) and it's knowledge of indexes, table information and in most databases cost of operation. Typical example is a NULL result which shows up in e.g. MySQL as 'Impossible WHERE noticed after reading const tables' which translates to: I've checked the where clause after applying primary -/ unique key joins and it gives no result... So I won't try to apply the other WHERE clauses as there cannot be a result....

:)

https://dev.mysql.com/doc/...

Here is a further explanation what query optimizer does...

@0x412E2047696C
https://dev.mysql.com/doc/refman/...

And here the part relating to joins.

@IntrusionCM woah, thanks!

I'll take a look in those resources :)

@Hubot-0x58
Lol. Yes. I was beginning to come to that conclusion myself.