It's at the temple of Apollo at Delphi:  "????? ???????—"Know Thyself."

Grading the introductory database course, I found that I had to learn something about myself and coding SQL after 30+ years of doing it.  And I had to correct it!

Helping students get a handle on JOINs, especially OUTER JOINs, is important and it presents many of them a conceptual challenge.  In one of our assignments, I found out my own challenge.

I am not "ambidextrous" with regard to outer joins.  I always tend to do LEFT OUTER JOINS, and I avoid RIGHT OUTER JOINS.  Sometimes that's just not right.

It started with an email to fellow teaching staff.  At this point in the class, should we encourage joins to subqueries?  That's how I had mentally solved a student exercise problem using a LEFT OUTER JOIN.  It's important to remember that each OUTER JOIN consists of an "OUTER side" and an "INNER side."  It's the "INNER side" identified by the LEFT/RIGHT denotation.  All tables to the LEFT of the leftmost LEFT OUTER JOIN are INNER.  To the right of a LEFT OUTER JOIN, all subsequent JOINS must be LEFT OUTER JOIN or weird things happen.  Similar but understandably reversed with RIGHT OUTER JOIN.

"No subquery needed," came the reply.  "You just start the JOIN chain with table xyz."  Well, that's not the INNER table (call it "abc") for the OUTER join.  And because I always use LEFT OUTER JOIN, I had to start with table abc.  Other tables, including xyz, needed to be INNER JOINed, and because they would be on the OUTER side, they would need to be INNER JOINed and made a subquery for the LEFT OUTER JOIN:

abc LEFT OUTER JOIN (select * from pqr JOIN tuv JOIN xyz) on ...

As I reframed the query in my mind per my colleague's note, I realized not only that I could only eliminate the subquery by using a RIGHT OUTER JOIN, I realized that I habitually and reflexively use LEFT OUTER JOIN even when it causes needless complications.  I think I've earned the right to be proud of my ability to make SQL do terrific things and even defy the expectations of relational databases.  However, for such cases I had used my skills to evade my own bias and work around the problems caused by that bias with needless complexity.  The way to address this problem was:

pqr JOIN tuv JOIN xyz RIGHT OUTER JOIN abc

Same results, simpler and better logic.

I was grateful that I got this correction, regardless that it could be thought embarrassing to be "shown up" by a younger and less experienced person.  The imperative to seek more information and more expertise as we continue this career is not just for the "latest and greatest."  It's also for finding the blind spots we never knew we had.  We need to cultivate expertise in our weaknesses and our biases to keep growing in this profession.  Thank you, colleague!