sql - how to get table from first table when data is not there in second table -

i have requirement need show data of both tables when both id's same.when id present in first table , not there in second table need show data first table

create table [dbo].[test](     [id] [int] null,     [name] [varchar](10) null,     [status] [char](1) null,     [createddate] [datetime] null ) on [primary]  go  create table [dbo].[test_history](     [id] [int] null,     [name] [varchar](10) null,     [status] [char](1) null,     [createddate] [datetime] null ) on [primary]  go /****** object:  table [dbo].[test_history]    script date: 06/19/2015 19:01:49 ******/ insert [dbo].[test_history] ([id], [name], [status], [createddate]) values (1, n'mohan', n'a', cast(0x0000a4bc01347e88 datetime)) insert [dbo].[test_history] ([id], [name], [status], [createddate]) values (1, n'mohan', n'i', cast(0x0000a4bc0134a390 datetime)) insert [dbo].[test_history] ([id], [name], [status], [createddate]) values (2, n'rohan', n'a', cast(0x0000a4bc01391fcc datetime)) /****** object:  table [dbo].[test]    script date: 06/19/2015 19:01:49 ******/ insert [dbo].[test] ([id], [name], [status], [createddate]) values (2, n'rohan', n'i', cast(0x0000a4bc0138d584 datetime)) insert [dbo].[test] ([id], [name], [status], [createddate]) values (1, n'mohan', n'a', cast(0x0000a4bc013072dc datetime)) insert [dbo].[test] ([id], [name], [status], [createddate]) values (3, n'raj', n'a', cast(0x0000a4bc0138ded7 datetime)) insert [dbo].[test] ([id], [name], [status], [createddate]) values (4, n'krishna', n'a', cast(0x0000a4bc0138ee31 datetime)) 

so far have tried query achieve result

select t.id,coalesce(t.id,tt.id),t.name,coalesce(t.name,tt.name),t.status,coalesce(t.status,tt.status)   test t left join (select top 1  id,min(name)name,status test_history  group  id,status  )tt  on t.id = tt.id   t.id = 3      id = 1 , 2 present show data both tables      id = 3 , 4 not present in table  using coalesce data  

from first table , show in 2nd table column

but there other way both tables same structure

i'm thinking of

declare @tablename varchar(10) if exists (select 1 test id = @id) if count there in both tables  set @tablename = test  else  set @tablename = test_history  select * @tablename  id = @id 

can solution

first: excellent setup data related question!

if real question if table variables can used described in question, answer no; or more accurately not worth it.

not recommended:

declare @tablename table ( [id] [int] null, [name] [varchar](10) null, [status] [char](1) null, [createddate] [datetime] null)  if exists (select 1 test id = @id)     insert @tablename select * dbo.test id = @id else insert @tablename select * dbo.[test_history] id = @id   select * @tablename  id = @id 

here's solution prefer:

declare @id int = 3; select * [dbo].[test]  ss ss.id = @id union select * [dbo].[test_history] th th.id = @id   , not exists ( select * [dbo].[test]  ss ss.id = @id); 

union all performs surprisingly - don't forget all keyword, , assuming id pk or ak.