It is a kind of connection. When you speak:
from customer in customers join order in orders on customer.Id equals order.CustomerId select whatever
which is essentially a more efficient way to write:
from customer in customers from order in orders where customer.Id == order.CustomerId select whatever
Do you understand why? The first one says that "the query processor, customers and orders have special relationships, which are determined by the equality of the customer identifier and the customer identifier stored in the order." The second says: "Give me a Cartesian product - all possible combinations of customers and orders - and then filter out those that make no sense." They have the same effect, but the first is more effective.
However, you can use several "from" clauses to make things more interesting than just Cartesian ones. Suppose a client can have more than one address:
from customer in customers from address in customer.Addresses select address
A few from clauses are actually "select many". . That is, they accept a sequence and a way to create sequences from each element of the first sequence and merge all the resulting sequences.
The "choice of many" is simple but extremely effective; we have already seen that you can use the "choose a lot" to perform a (slow but correct) join operation. In fact, you can use select many to make every possible request if you are smart enough and don't mind spending a lot of time and memory. For instance:
from customer in customers where customer.City == "London" select customer
can be written without "where", for example:
from customer in customers from c in (customer.City == "London" ? new Customer[] {customer} : new Customer[] { } ) select c;
You would be crazy to do this, but where and join not really needed - these are simply faster, shorter, and more efficient ways of writing choices.