declare @products TABLE (
[name] nvarchar(50),
[size] smallint,
[cat] nvarchar(50),
[cond] nvarchar(50),
[created] datetime not null default getdate()
);

declare @SizeRanges1  TABLE (
LimL1 smallint,
LimU1 smallint,
Description1 varchar(20)
);
declare @SizeRanges2  TABLE (
LimL2 smallint,
LimU2 smallint,
Description2 varchar(40)
);

insert into @Products(name, size, cat, cond) values(N'Product1', 20,  N'Phone1', N'OK');

insert into @Products(name, size, cat, cond) values(N'Product1', 20,  N'Phone1', N'OK');
insert into @Products(name, size, cat, cond) values(N'Product1', 20,  N'Phone1', N'OK');
insert into @Products(name, size, cat, cond) values(N'Product2', 400, N'Note2', N'OKOK' );
insert into @Products(name, size, cat, cond) values(N'Product3', 255, N'Note', N'OK');
insert into @Products(name, size, cat, cond) values(N'Product4', 385, N'Note', N'OK');
insert into @Products(name, size, cat, cond) values(N'Product5', 420, N'Note', N'SOSO');
insert into @Products(name, size, cat, cond) values(N'Product5', 420, N'Note', N'OK');
insert into @Products(name, size, cat, cond) values(N'Product7', 8800, N'No', N'OK');

insert into @SizeRanges1(limL1, LimU1, Description1) values (0, 25, N'Малый');
insert into @SizeRanges1(limL1, LimU1, Description1) values (26, 100, N'Маленький');
insert into @SizeRanges1(limL1, LimU1, Description1) values (405, 418, N'Средний');
insert into @SizeRanges1(limL1, LimU1, Description1) values (600, 700, N'Большой');
insert into @SizeRanges2(limL2, LimU2, Description2) values (200, 280, N'Элька');
insert into @SizeRanges2(limL2, LimU2, Description2) values (300, 500, N'Эмка');
insert into @SizeRanges2(limL2, LimU2, Description2) values (1000, 5000, N'Офигенный');

select * from @Products p inner join @SizeRanges1 n on p.Size between n.LimL1 and n.LimU1
union all
select * from @Products p inner join @SizeRanges2 n on p.Size between n.LimL2 and n.LimU2
union all
select p.*, '0' as LimL3,  '0' as LimU3, N'XEZ' as Description3  from @Products p where 
(
	not exists(select 1 from @SizeRanges2 n2 where p.Size between n2.LimL2 and n2.LimU2)
	and 
	not exists(select 1 from @SizeRanges1 n1 where p.Size between n1.LimL1 and n1.LimU1)
)

select * from @Products p inner join @SizeRanges1 n on p.Size between n.LimL1 and n.LimU1
union all
select * from @Products p inner join @SizeRanges2 n on p.Size between n.LimL2 and n.LimU2
union all
select p.*, '0' as LimL3,  '0' as LimU3, N'XEZ' as Description3  from @Products p where 
(
	not exists(select 1 from @SizeRanges2 n2 where p.Size between n2.LimL2 and n2.LimU2)
	and 
	not exists(select 1 from @SizeRanges1 n1 where p.Size between n1.LimL1 and n1.LimU1)
)

select P.*, ISNULL(S.Description1, 'XEZ') SIZE_DESC 
	from @Products P
OUTER APPLY 
(SELECT TOP 1 * FROM 
(
SELECT limL1, LimU1, Description1, 1 as N FROM @SizeRanges1 
UNION 
SELECT *, 2 FROM @SizeRanges2 
) T   WHERE P.Size between T.LimL1 and T.LimU1 
ORDER BY T.N
) S

SELECT DISTINCT
       t0.*,
       COALESCE(t1.Description1, t2.Description2, 'XYZ') Description
FROM Products t0
LEFT JOIN SizeRanges1 t1 ON t0.size BETWEEN t1.LimL1 AND t1.LimU1
LEFT JOIN SizeRanges2 t2 ON t0.size BETWEEN t2.LimL2 AND t2.LimU2;