如何从两个表中选择不同的表，然后连接它们

Question

我见过类似的问题，但没有一个适合我的问题。

我有两个表:

工作台

tbody> <<tr>

WorkCode	WorkDescription
220001	WorkDescription1
220002	WorkDescription2
220003	WorkDescription3
220004	WorkDescription4

这不是很优雅，但见下文。

--This section is just creating the temp tables with the data you provided.
DECLARE @WorkTable TABLE (
WorkCode INT, 
WorkDescription NVARCHAR(50))
INSERT INTO @WorkTable (WorkCode, WorkDescription)
VALUES (220001, 'WorkDescription1') , (220002, 'WorkDescription2'), (220003, 'WorkDescription3'),  (220004, 'WorkDescription4')

DECLARE @TasksTable TABLE (
TaskCode NVARCHAR(50), 
WorkCode INT ,
TaskDescription NVARCHAR(50))
INSERT INTO @TasksTable (TaskCode, WorkCode, TaskDescription)
VALUES ('AV0001', NULL, 'TaskDescription1') , ('AV0002', 220002, 'TaskDescription2'), ('AV0003', 220003, 'TaskDescription3'),  ('AV0004', 220003, 'TaskDescription4')
;
--Actual SQL query starts here
WITH a as (
select distinct w.WorkCode, t.TaskCode, t.TaskDescription, w.WorkDescription
from @WorkTable w
LEFT JOIN @TasksTable t
on t.WorkCode = w.WorkCode) ,
b as (
SELECT distinct a.WorkCode, tt.TaskCode, tt.TaskDescription, a.WorkDescription
FROM @TasksTable tt
left join a 
on tt.WorkCode = a.WorkCode) ,
c as (

select *
from a
union all 
select *
from b)
select distinct c.WorkCode, c.TaskCode, c.TaskDescription, c.WorkDescription
from c