The internals of any query operation can be reviewed in the query execution plan. There are many factors involved in how the data is extracted and queried from the source tables, but for the purposes of your question, there are two elements you need to be concerned about:
Seeks and Scans
The first step to any query is to extract the data from the source tables. This is performed using one of two actions (and variations within): a seek or a scan. A seek is where the query engine can leverage an index to narrow down the data retrieval. If no index is available, then the engine must perform a scan of all records in the table. In general, seeks typically perform better than scans due because it is more selective.
Once data is retrieved from the tables, it must be physically joined. Note, this is not the same as logical joins (
OUTER). There are three physical join operators that can be used by the engine:
The query engine will evaluate several factors to determine which physical join will be the most efficient for the query execution. As a simple example, I assembled this SQL Fiddle example, where you can view a basic execution plan using your sample schema. Note, however, that this plan could change with more data being queried. You will need to review the query plans in your environment to truly understand how your data is being retrieved.
Query plan analysis and performance tuning is beyond the scope of this answer, but I strongly recommend getting a hold of Grant Fritchey' SQL Server 2012 Query Performance Tuning for a comprehensive explanation of query execution internals and tuning approaches.