declare @a  table (id int, order_id int)
declare @b  table (order_id int, name varchar( 10 ))
insert into @a
select  1 , 1  union all
select  2 , 2  union all
select  3 , 0  union all
select  4 , 0  

insert into @b
select  1 ,'a' union all
select  2 ,'b'

select a.id,a.order_id,b.name from @a a,@b b where a.order_id*=b.order_id

id          order_id    name       
 ----------- ----------- ---------- 
 
 1             1            a
 2             2            b
 3             0            NULL
 4             0            NULL

declare @a  table (id int, order_id int)
declare @b  table (order_id int, name varchar( 10 ))
insert into @a
select  1 , 1  union all
select  2 , 2  union all
select  3 , 0  union all
select  4 , 0  

insert into @b
select  1 ,'a' union all
select  2 ,'b'

select a.id,a.order_id,b.name from @a a left join @b b on a.order_id=b.order_id