With the Join calls in content manager queries, you just include the joined part's properties in the SQL query, to prevent an extra roundtrip. Just for performance. DB tables are joined on content ID, which is a shared key among content part records of the
same content item.

As<Type>() is an extension method implemented in Orchard.ContentManagement.ContentExtensions.cs file. If you search for references in the orchard code you can find many examples. For examples of using this with CommonPart, you can use search in Visual
Studio for .As<CommonPart>()