sql - What is the optimal select according to you? -

what optimal select according you?

select @mailto=isnull((select data filedata descid=3104 , dataid=evt04) ,'')  event_21   evtid=@nfileid 

or

select @mailto=isnull(data ,'')  event_21   innerjoin filedata  on event_21.evt04=filedata.dataid descid=3104    , evtid=@nfileid 

obviously "join" faster "inline select query". have tested 1000 rows. can test. here sample test code.

create table [dbo].[tablea](     [id] [int] identity(1,1) not null,     [name] [nvarchar](100) not null,  constraint [pk_tablea] primary key clustered  (     [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go  create table [dbo].[tableb](     [rowid] [int] identity(1,1) not null,     [id] [int] not null,     [designation] [nvarchar](100) null,  constraint [pk_tableb] primary key clustered  (     [rowid] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go  declare  @min       int = 1         ,@max       int = 1000  while (@min <= @max) begin     insert tablea(name)     select  'name name of - ' + cast(@min nvarchar(10))      insert tableb(id,designation)     select   @min             ,'desig name of - name' + cast(@min nvarchar(10))      select  @min = @min + 1 end 

first @ inline select query

-- inline query select   a.id         ,a.name         ,(select designation tableb b b.id = a.id) designation    tablea (nolock) 

output:

execution plan:

now join query :-

---- join  select   a.id         ,a.name         ,b.designation    tablea (nolock)         join tableb b on a.id = b.id 

execution plan join query :- can see clear difference.