Better SQL JOINs
In this example you provided, if Foreign Keys are to be used by their defined name, why would we need to specify which table we&#x27;re going to join here?<p><pre><code>    FROM permission p
    LEFT JOIN role r WITH p-&gt;permission_role_id_fkey = r
    LEFT JOIN team_role tr WITH tr-&gt;team_role_role_id_fkey = r
    LEFT JOIN team t WITH tr-&gt;team_role_team_id_fkey = t
    LEFT JOIN user_role ur WITH ur-&gt;user_role_role_id_fkey = r
    LEFT JOIN &quot;user&quot; u WITH ur-&gt;user_role_user_id_fkey = u
    WHERE p.id = 1;
</code></pre>
Why should this not simply be:<p><pre><code>    FROM permission p
    LEFT JOIN role r USING p.permission_role_id_fkey
    LEFT JOIN team_role tr USING tr.team_role_role_id_fkey
    LEFT JOIN team t USING tr.team_role_team_id_fkey
    LEFT JOIN user_role ur USING ur.user_role_role_id_fkey
    LEFT JOIN &quot;user&quot; u USING ur.user_role_user_id_fkey
    WHERE p.id = 1;
</code></pre>
The foreign keys here already define the relationships between tables.<p>To be clear though; there&#x27;s a really good set of reasons this isn&#x27;t a great idea IMO, the two simplest being:<p>1) Now when I&#x27;m writing queries I have to go look up the foreign key names. This isn&#x27;t something that I&#x27;m often innately familiar with (unless there&#x27;s a reliable naming convention; spoiler: there never seems to be). Contrasted with field names, I could easily construct half a dozen quick queries against any DB I regularly use by knowing which tables or fields are there.<p>2) The example you provide are actually less clear than explicitly stating the joined fields. If I have to come in and troubleshoot something down the line, I then have to spend additional time after viewing the query going in search of what the heck fields are involved in team_role_role_id_fkey, rather than just seeing that it&#x27;s role.id and team_role.role_id.