Ok I think I’ve wrapped my head around the table join.
In a relational database tables are linked by primary and foreign keys. The join statement just tells us how which two columns are used to link the two tables.
In an entity relation diagram the two tables are joined with a line, usually the left table has one entry to many or none of the right table the tables are linked by keys.
The join statement describes this relationship.
I’m not sure I grasp left join vs right join. I believe the diagram above the Patient is the left table and Study is the right table.
I can’t imagine a use for a right join on this diagram.
LEFT JOIN… keep ALL records from the LEFT (first) table that meet the WHERE clause, adding FIELDS from the RIGHT (second) table that meet the ON clause, setting those fields to NULL (or default values) where the ON clause is NOT met
RIGHT JOIN… the reverse of LEFT JOIN (take above definition and swap the words (first and second))
In the 30+ years I have been writing SQL, I have NEVER used a RIGHT join… simply crafted the query to be a LEFT JOIN where required.