Select s.SubTabId, m.mainTabId, m.mType From SubType s 
INNER JOIN mainTab m 
ON s.mainTabId = m.mainTabId
Where s.userId = 2
UNION
Select 0, mainTabId, mType From mainTab as m
where not exists 
	(select *
	 from SubType as s
	 where userID = 2
	 and s.mainTabId = m.mainTabId)
Order By SubTabId DESC

declare @m table (mainTabId int, mType char(2))
insert @m values 
(1, 'k1'),
(2, 'k2'),
(3, 'k3'),
(4, 'k4')
declare @s table (subTabId int, mainTabId int, subType char(2), userID int)
insert @s values 
(1, 1, 's1', 1),
(2, 2, 's2', 2),
(3, 3, 's3', 3)

select  isnull(d.SubTabId,0) as SubTabId, 
	  m.mainTabId, 
	  m.mType
from @m as m 
  left outer join
    (Select s.SubTabId, 
		m.mainTabId, 
		m.mType 
     From @s as s
	inner join @m as m
        on s.mainTabId = m.mainTabId
     Where s.userId = 1) as d
      on m.mainTabId=d.mainTabId