select b.id,case when a.a_id is null then b.b_name end as b_name
from b
left join a on a.a_name=b.b_name

------解决方案--------------------
select id,replace(a.aname,b.bname,'')
from a cross join b

截取都，前面还有一个逗号，自己再处理一下吧

------解决方案--------------------
B表B_Name列找逗号切割，在替换A表里的A_Name

------解决方案--------------------
没有做优化处理，可能写的比较繁琐
而且2000 不能用，只适用于2000以上的版本

SQL code
--> 测试数据：#A
IF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #A
GO 
CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))
INSERT #A
SELECT 1,'a,b,c,d'
--> 测试数据：#B
IF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #B
GO 
CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))
INSERT #B
SELECT 1,'a' UNION ALL
SELECT 2,'a,b' UNION ALL
SELECT 3,'a,b,c,d' UNION ALL
SELECT 4,'b,d' UNION ALL
SELECT 5,'b,c,d' UNION ALL
SELECT 6,'a,d' UNION ALL
SELECT 7,'a,b,c'
--------------开始查询--------------------------

; WITH cte AS(
SELECT  T.c.value('.' , 'varchar(10)') AS nameA
FROM    (
         SELECT CAST( '<x>'+ REPLACE ([A_Name] , ',' , '</x><x>')+ '</x>' AS XML) AS name FROM #A
        ) A
CROSS APPLY A.name.nodes('/x/text()') T (c)
 )
,cte2 AS(
SELECT  [B_Id] , T.c.value('.' , 'varchar(10)') AS nameB , row_id = ROW_NUMBER() OVER (PARTITION BY [B_Id] ORDER BY T.c.value('.' , 'varchar(10)'))
FROM    (
         SELECT [B_Id] , CAST( '<x>'+ REPLACE ([B_Name] , ',' , '</x><x>')+ '</x>' AS XML) AS name FROM #B
        ) B
CROSS APPLY B.name.nodes('/x/text()') T (c)
)
,cte3 AS 
( 
SELECT  DISTINCT
        [B_Id] , nameA
FROM    cte2 AS t
OUTER  APPLY (
              SELECT * FROM cte WHERE nameA NOT IN( SELECT nameB FROM cte2 WHERE [B_Id]= t.[B_Id])
             ) app

)
SELECT [B_Id],[B_Name]=STUFF((SELECT ','+nameA FROM cte3 WHERE [B_Id]=t.[B_Id] ORDER BY nameA FOR XML PATH('') ),1,1,'') FROM cte3 AS t
GROUP BY [B_Id]
ORDER BY [B_Id]
 
    
----------------结果----------------------------
/* 
B_Id    B_Name
1    b,c,d
2    c,d
3    NULL
4    a,c
5    a
6    b,c
7    d
*/

------解决方案--------------------
SQL code
--> 测试数据：#A
IF OBJECT_ID('TEMPDB.DBO.#A') IS NOT NULL DROP TABLE #A
GO 
CREATE TABLE #A([A_Id] INT,[A_Name] VARCHAR(7))
INSERT #A
SELECT 1,'a,b,c,d'
--> 测试数据：#B
IF OBJECT_ID('TEMPDB.DBO.#B') IS NOT NULL DROP TABLE #B
GO 
CREATE TABLE #B([B_Id] INT,[B_Name] VARCHAR(7))
INSERT #B
SELECT 1,'a' UNION ALL
SELECT 2,'a,b' UNION ALL
SELECT 3,'a,b,c,d' UNION ALL
SELECT 4,'b,d' UNION ALL
SELECT 5,'b,c,d' UNION ALL
SELECT 6,'a,d' UNION ALL
SELECT 7,'a,b,c'
 
---写个2000的，用函数分割替换 
 create function f_name(@name varchar(20),@rename varchar(20))
 returns varchar(20)
 begin
      select @name=@name+',',@rename=','+@rename
      while charindex(',',@name)>0
      begin
          set @rename=replace(@rename,','+left(@name,charindex(',',@name)-1),'')
          set @name=right(@name,len(@name)-charindex(',',@name))  
      end
      return stuff(@rename,1,1,'')
 end
 


 select n.B_ID,
        B_name=dbo.f_name(n.B_name,m.A_name)
 from #A m cross join #B n
 
 /*
 B_ID        B_name
----------- --------------------
1           b,c,d
2           c,d
3           NULL
4           a,c
5           a
6           b,c
7           d

(7 row(s) a