I would suggest you read a book about SQL Server - Professional SQL Server 2012 Internals and Troubleshooting by Bolton, Langford, Berry, et al, is a good one.
First of all I want to tell you that you don't want to go core-crazy when designing a SQL 2012 server. Microsoft has changed its licensing model to per-core on SQL Server 2012, which bodes very poorly for AMD processors, which currently favor a higher number of relatively weaker cores, versus Intel processors which often have fewer physical cores but perform much better per core.
You can spend twice as much in licensing cost for a server with more AMD cores, or spend a little more on an Intel server with fewer cores and save a hundred thousand dollars in licensing and end up with a server that performs better.
A huge query with multiple joins like the one you described would generate a complex execution plan, be broken up into tasks by the query optimization process, and executed in parallel. So yes, that sort of query would definitely take advantage of the parallelism provided by multiple processors/cores.
Edit: Current generation Intel processors are just flat better than AMD processors on SQL 2012 workloads. Look at the TPC-E benchmarks on tpc.org. AMD processors simply cannot match the single-threaded performance of Intel processors at this time. AMD processors typically have more cores, but it does not make up the gap. In addition, lots of cores on SQL Server show diminishing returns, regardless of the manufacturer.
Also, read my blog post on this topic here.