Use of relationships (joins) in Views
Views is an awesome query builder and formatter - all through the UI. In my past coding days I did that all manually and recently I started to appreciate the power of views.

Just go into views settings / basic and turn on "Show the SQL query". Then create a basic view of nodes. Now add fields and filters to see how the SQL changes. Add a relationship and you'll see how views adds the "SQL Joins" automatically.

When you add some "fields" it will automatically add some joins without you having to add relationships. For example add "last comment author" field to a node and views will automatically add:

INNER JOIN {node_comment_statistics} ...
LEFT JOIN {users} ....


When you want to add some fields that you don't see in the selection, for example "author of the node", you add the relationship "Content: Author - Relate content to the user who created it."
Views adds the SQL:

INNER JOIN {users} ....


Now you'll have the user related fields available, for example "(author) User: Picture (Picture)".

So in summary: most views will pull data from multiple tables but most of that detail is hidden from the developers because views does it all for you. If you understand tables and SQL turn on the SQL output and see what views does. Sometimes all those "joins" are not optimal from an optimization standpoint ;-)