sql - Can correlated subquery following is not null check be replaced with joins? -

can correlated subquery, subquery follows not null check on foreign key, replaced joins? example:

select * tableabc t  (t.label_id null or t.label_id in ( select t1.id labels t1 t1.type = '123')) , (t.tag_id null or t.tag_id in ( select t2.id tags t2 t2.type = '123')) 

described words: let's say, i'm looking records if have label reference defined label must of type; same apply tags.

or query improved other means?

it intended tsql (ms sql).

update:
have added table aliases hinted habo. hope improve readability.

i'm not sure improvement, use left outer join instead of correlated subqueries.

-- sample data. declare @tableabc table( abcid int identity, labelid int, tagid int ); declare @labels table( labelid int identity, label varchar(3) ); declare @tags table( tagid int identity, tag varchar(3) );  insert @labels ( label ) values ( '123' ), ( '12' ), ( '123' ); insert @tags ( tag ) values ( '123' ), ( '213' ), ( '123' ), ( '312' ); insert @tableabc ( labelid, tagid ) values   ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 1, 4 ),   ( 2, 1 ), ( 2, 2 ), ( 2, 3 ), ( 2, 4 ),   ( 3, 1 ), ( 3, 2 ), ( 3, 3 ), ( 3, 4 ),   ( null, 1 ), ( null, 3 ), ( 1, null ), ( 3, null ), ( null, null );  select abc.abcid, abc.labelid, abc.tagid,   l.labelid l_labelid, l.label l_label,   case when abc.labelid null or l.label = '123' '<<<' else '' end 'l_match',   t.tagid t_tagid, t.tag t_tag,   case when abc.tagid null or t.tag = '123' '<<<' else '' end 't_match'   @tableabc abc left outer join     @labels l on l.labelid = abc.labelid left outer join     @tags t on t.tagid = abc.tagid;  -- "original" query: select *   @tableabc   ( labelid null or labelid in ( select labelid @labels label = '123' ) ) ,     ( tagid null or tagid in ( select tagid @tags tag = '123' ) );  -- left outer joins: select abc.*   @tableabc abc left outer join     @labels l on l.labelid = abc.labelid , l.label = '123' left outer join     @tags t on t.tagid = abc.tagid , t.tag = '123'   ( abc.labelid null or l.labelid not null ) , ( abc.tagid null or t.tagid not null ); 

tip: use helpful table aliases joins , apply them columns.