Join or nested select

I need to get some data from table1 and only one column from table2. What query is better? Is the开发者_运维技巧re any difference in performance etc?

select t1.Col1, t1.Col2, 
    (select t2.Name from table2 t2 where t1.Code = t2.Code) as 'Name'
from table1 t1

or

select t1.Col1, t1.Col2, t2.Name
from table1 t1
    left join table2 t2 on t1.Code = t2.Code 

They should optimise to the same query in most cases, but not always. When they don't, the sub-query approach will kill performance because it is executed for each row in table1.

Personally I prefer the JOIN syntax
What if you have many rows in table2 for each Code in tables. And performance as mentioned

Your first answer is an example of something called a correlated subquery. These can be amongst the most performance-sucking things you will come across. As such, the second is in theory way better.

Having said that, SQL Server is excellent at optimising this stuff away, particularly for equality conditions like yours above. Try looking at both using the Estimated Execution Plan, and you'll see that they're probably both given a similar optimisation.