So, instead of thinking of the query as implicitly asking for a
LEFT JOIN, as we would have to explicitly state in SQL, the SOQL version is instead explicitly asking for the related objects connected to the principle object of the query. The actual magic that goes on in the lower levels of the system do include things like joins, obviously, but they are vastly more complicated than you might realize. The queries have to join and filter against multiple tables, such as sharing settings, user permissions, and more. The SOQL API expresses all of that complicated query logic as a simple object that represents the relationship between two things. As a simple real-life metaphor, if I ask you to list the names of a friend's children, you wouldn't mentally look up the friend, then every child you know, and then use some index table to find the relationships. You'd just recall your friend and their children through the relationship.
At the end of the day, it doesn't matter how the underlying queries are structured. All you need to understand is that we are talking about objects and relationships, not tables in a database. Every object you interact with in the API or the UI has multiple tables that hold all kinds of data. All of that complexity is tucked away, leaving you with a simple interface to find the data you're looking for in a cohesive, organized manner.