SQL query to get both common and and non common data from 2 tables

Hi im looking for a query which will give me both common and non-common data in one query.

Table 2

ID	Assay
1	124

Result

required_missing	required_present
125	124

Based on req_ind column from table 1 , if req_ind is 1 and the same assay is present in table 2 i want to list it as above.

required missing column can have multiple column.

Answer

With the data given this gives requested result:

WITH table1 as (
   select 1 as ID, 123 as Assay, 0 as req_ind from dual
   union all
   select 2,124,1 from dual
   union all
   select 3,125,1 from dual
   ),
table2 as (
   select 1 as ID, 124 as Assay from dual
),
required_missing as (
   select 
      row_number() over (order by table1.Assay) as R,
      table1.Assay as required_missing
   from table1
   left join table2 on table2.Assay = table1.Assay
   where table1.req_ind=1 and table2.id is null
),
requires_present as (
   select 
      row_number() over (order by table1.Assay) as R,
      table1.Assay as required_present
   from table1
   left join table2 on table2.Assay = table1.Assay
   where table1.req_ind=1 and table2.id is not null
),
results as (
   select row_number() over (order by (id)) as r
   from table1
)
select rm.required_missing, rp.required_present
from results
left join required_missing rm on rm.R = results.R
left join requires_present rp on rp.R = results.R
where rm.R is not null or rp.R is not null;

output:

REQUIRED_MISSING	REQUIRED_PRESENT
125	124